Export xml data using BCP Command in SQL Server

Question

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

On a side note, the documentation explicitly says The -x does not work when importing or exporting data. — Bridge
– Bridge, Commented Jan 19, 2016 at 8:50

SteveC · Accepted Answer · 2019-06-06 12:49:24Z

9

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';

edited Jun 6, 2019 at 12:49

SteveC

17k25 gold badges116 silver badges180 bronze badges

answered Nov 26, 2014 at 15:44

Brad D

7624 silver badges8 bronze badges

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

Comments

Dan Guzman · Accepted Answer · 2014-11-26 14:02:08Z

1

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';

answered Nov 26, 2014 at 14:02

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

2 Comments

Shashikant Over a year ago

Thanks for replay I tried this but it is also not working,following message i got after execution command NULL Starting copy... NULL 0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 NULL

RThomas Over a year ago

This command worked great for a very similar effort for exporting Extended Event outputs to an external file. +1

Ian Boyd · Accepted Answer · 2022-02-11 14:52:01Z

1

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;

edited Feb 11, 2022 at 14:52

Ian Boyd

259k271 gold badges920 silver badges1.3k bronze badges

answered Jan 19, 2016 at 8:46

Amarjeet Singh

111 bronze badge

Collectives™ on Stack Overflow

Export xml data using BCP Command in SQL Server

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related