Using BCP to Export SQL Data to XML file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Using BCP to Export SQL Data to XML file

Author

Topic

9pepper
Starting Member

4 Posts

Posted - 2012-02-17 : 13:42:55

I tried to export sql data to a xml file using the script below. The command successfully completes in SQL management studio, but no file has been generated in the destination folder ('c:\temp\). I also tried using the PRINT @bcpCommandto get the actual value of @bcpCommand and run it at window command prompt, and it successfully generated the file at the destination folder.

Please help explain why this command "EXEC master..xp_cmdshell @bcpCommand, No_output" did not generate the file.

if you have Northwind database, you can run to test, just make sure change the correct server name.

Thanks!

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\temp\BCP_OUT_XML_'+CONVERT(char(8),GETDATE(),1)+'.xml','/','-')

SET @bcpCommand = 'bcp "SELECT CategoryID, CategoryName FROM Northwind.dbo.Categories [Categories]
FOR XML AUTO, ROOT(''NewDataSet''), ELEMENTS" QUERYOUT "'

SET @bcpCommand = @bcpCommand + @FileName + '" -SMAIN\SQL2008 -T -c -r -t'
--PRINT @bcpCommand
EXEC master..xp_cmdshell @bcpCommand, No_output
go

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-17 : 17:53:07

Not clear to me whether the SET @bcpCommand statement in your posting is actually on two lines in your SSMS window, or whether it is really on one line, but is appearing as two lines in the posting. In any case, that needs to be on a single line, as in:

SET @bcpCommand = 'bcp "SELECT CategoryID, CategoryName FROM Northwind.dbo.Categories [Categories] FOR XML AUTO, ROOT(''NewDataSet''), ELEMENTS" QUERYOUT "'

9pepper
Starting Member

4 Posts

Posted - 2012-02-17 : 18:48:12

Yes, it is single line, and welcom to test the script to see it yourself.

I need a similar script for my production database, so please help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-18 : 08:14:26

I tried it, and it works for me. However, I did not have Northwind, so I tested it using a silly table that I have in my test database. My exact query is this:

DECLARE @FileName varchar(50),
@bcpCommand varchar(8000)

SET @FileName = REPLACE('c:\temp\BCP_OUT_XML_'+CONVERT(char(8),GETDATE(),1)+'.xml','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM MyDB.dbo.TimeTable [Categories] FOR XML AUTO, ROOT(''NewDataSet''), ELEMENTS" QUERYOUT "'

SET @bcpCommand = @bcpCommand + @FileName + '"  -T -c -r -t'
PRINT @bcpCommand
EXEC master..xp_cmdshell @bcpCommand , No_output
go

Make sure that C:\Temp exists and that the file, if already existing is not locked by an editor or other process.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-18 : 08:55:12

Also, remove the ", N_output" directive and run the query so you can see what the error message if any is printed out.

9pepper
Starting Member

4 Posts

Posted - 2012-02-19 : 01:08:46

Sunitabeck,

I modified your query to change the database, then ran, but it did not work. I meant the SQL Management studio show the message "Command(s) completed successfully." But it did not generate the file at the destination folder.

You did not include server name in your query, so I could not test directly from windows command prompt.

Again both your and my did show: "Command(s) completed successfully." but failed to generated the file. I can use the value of my @bcpCommand to run at windows command prompt, and it did successfully generate the file at the destination folder.

Please help.

9pepper
Starting Member

4 Posts

Posted - 2012-02-19 : 01:25:48

I found it. You absolutely right at the very beginning, it should be on one line for the whole command.

I am so sorry for my terrible mistake taking all our readers' time.

Again to everyone: The command should be on one line as below:

SET @bcpCommand = 'bcp "SELECT CategoryID, CategoryName FROM Northwind.dbo.Categories [Categories] FOR XML AUTO, ROOT(''NewDataSet''), ELEMENTS" QUERYOUT "'

make sure from SET to QUERYOUT "' on the same line.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources