Export xml data using BCP Command in SQL Server

19.6k views Asked by At

I am trying to export datable data in xml format but,Problem is like i can able to create xml file but data is not get writing in to the file following is my code I am trying.

DECLARE @cmd  VARCHAR(2000);

SET @cmd = 'bcp.exe "select * from emp FOR XML AUTO" queryout E:\rahul_1.xml -x -T';

EXEC xp_cmdshell  @cmd ;

And following is the output message I am getting after executing above code

NULL
Enter the file storage type of field XML_F52E2B61-18A1-11d1-B105-00805F49916B [ntext]: 

can any body please suggest me on this

3

There are 3 answers

0
Brad D On BEST ANSWER

Dan you answer works, except one last thing. BCP needs additional information about the source query. Best idea to fully qualify the source of the data.

SET @cmd = 'bcp.exe "select * from [Database].[Schema].[Table] FOR XML AUTO" 
             queryout E:\rahul_1.xml -c -T';
2
Dan Guzman On

Instead of the -x parameter (generate xml format file), specify -c (character file):

SET @cmd = 'bcp.exe "select * from emp FOR XML AUTO" queryout E:\rahul_1.xml -c -T';
0
Amarjeet Singh On

Try to use -w switch for exporting XML file in correct format

DECLARE @cmd VARCHAR(2000);

SET @cmd = 'bcp.exe "select * from [Database].[Schema].[Table] FOR XML AUTO" queryout E:\filename.xml -S MyServer\MyInstance -c -T -w';

EXEC xp_cmdshell @cmd;