I have a stored procedure generating XML with FOR XML functionality. When I run the stored procedure, I get beautifully formatted XML as a result.
When I try to export this result into a file with bcp
declare @sql varchar(8000)
select @sql = 'bcp "exec sp_ExportXml" queryout C:\Filename.xml -S (local) -T -w'
exec master..xp_cmdshell @sql
I get a badly formatted with line breaks inside the xml tags e.g.
3
sion>
I am completely clueless, I tried several different parameters for bcp queryout but always getting the same result.
Any ideas are highly appreciated! Thanks
解决方案
I could solve this by not using bcp but instead run ExecuteXmlReader on the SqlCommand as suggested here: http://support.microsoft.com/kb/310378