Trying to send sql results to file in XML format

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

Transact-SQL (2008)

Trying to send sql results to file in XML format

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2011-04-08 : 08:50:20

Hi,
I use this to send SQL results to a text file:

SELECT @bcp = 'bcp " Select * from NOTTHWIND.dbo.##CustomersOut " queryout C:\Bad_file\Dup_Cust.txt -T -c -S' + @@servername
exec Master..xp_cmdshell @bcp

But I cannot seem to to get it to output results as an XML file. Can someone tell me how to do this.
Thank you

ITM

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-08 : 09:51:29

SELECT @bcp = 'bcp " Select * from NOTTHWIND.dbo.##CustomersOut FOR XML AUTO, ROOT" queryout C:\Bad_file\Dup_Cust.txt -T -c -S' + @@servername
exec Master..xp_cmdshell @bcp

If the layout doesn't meet your needs you can alter it using XML PATH options. Books Online has details.

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2011-04-08 : 10:00:29

quote:
Originally posted by robvolk

SELECT @bcp = 'bcp " Select * from NOTTHWIND.dbo.##CustomersOut FOR XML AUTO, ROOT" queryout C:\Bad_file\Dup_Cust.txt -T -c -S' + @@servername
exec Master..xp_cmdshell @bcp

If the layout doesn't meet your needs you can alter it using XML PATH options. Books Online has details.

Thanks That did it

The only problem is that it is a text file, can it be made a xml file?
This is what I now have:

ITM

Declare @bcp varchar(150)
SELECT @bcp = 'bcp " Select * from NOTTHWIND.dbo.[Customers] FOR XML AUTO, ROOT" queryout c:\sample.txt -T -c -S' + @@servername
exec Master..xp_cmdshell @bcp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources