0

I am trying to export the xml generated from sql query. I am trying like this

 EXEC master.dbo.sp_configure 'show advanced options', 1
 RECONFIGURE
 EXEC master.dbo.sp_configure 'xp_cmdshell', 1
 RECONFIGURE 
 EXEC xp_cmdshell 'bcp "SELECT @xml" -S <SERVERNAME> -D <DATABASE_NAME> -U <UserName> -P <PASSWORD> queryout "E:\test.xml" -T -c -t,'

while trying this I got the result listed below

 Copy direction must be either 'in', 'out' or 'format'.
 usage: bcp {dbtable | query} {in | out | queryout | format} datafile
 [-m maxerrors]            [-f formatfile]          [-e errfile]
 [-F firstrow]             [-L lastrow]             [-b batchsize]
 [-n native type]          [-c character type]      [-w wide character type]
 [-N keep non-text native] [-V file format version] [-q quoted identifier]
 [-C code page specifier]  [-t field terminator]    [-r row terminator]
 [-i inputfile]            [-o outfile]             [-a packetsize]
 [-S server name]          [-U username]            [-P password]
 [-T trusted connection]   [-v version]             [-R regional enable]
 [-k keep null values]     [-E keep identity values]
 [-h "load hints"]         [-x generate xml format file]
 [-d database name]        [-K application intent]

I am not been able to find out the problem.

My questions are-

  1. What I am doing wrong?

  2. How to save/export xml in the file in drive?

1 Answer 1

1

I'm not sure what you're trying to do with this command:

exec xp_cmdshell 'bcp "SELECT @xml" -S <SERVERNAME> -D <DATABASE_NAME> -U <UserName> -P <PASSWORD> queryout "E:\test.xml" -T -c -t,

What I can tell you is immediately wrong:

  • You are mixing authentication with -T and also -U -P

  • Your query "select @xml" isn't valid

You could try something like:

exec xp_cmdshell 'bcp "Select [test1]=1 for xml path" queryout e:\test.xml -T -c'

test.xml would look like:

 <row><test1>1</test1></row>

And build up to what kind of output you are looking for.

By default xp_cmdshell executes on the server as the sql server service account, although a proxy account can be set up.

You might want to look into for xml to make sure you telling sql server how you want to xml to be formated.

References:

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.