XML Save/Export - SQL Server - Copy direction must be either 'in', 'out' or 'format'

Question

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?

Answer 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:

• Import and Export Bulk Data by Using the bcp Utility (SQL Server) - MSDN

• bcp Utility - MSDN

• xp_cmdshell - MSDN

• For XML (SQL Server)

• Using the Bulk-Load Tools in SQL Server - Erland Sommarskog