Search code examples
sql-serverxmlbcp

SQL Server Bcp Export XML Format

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. 

<Division>3</Divi
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

Solution

  • I could solve this by not using bcp but instead run ExecuteXmlReader on the SqlCommand as suggested here: http://support.microsoft.com/kb/310378