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
Is the simple solution here just to use the -r switch?
I faced the same issue and was thinking there must be a simple answer, after some searching around I gave it a go and it worked for me.
select @sql = 'bcp "exec sp_ExportXml" queryout C:\Filename.xml -S (local) -T -w -r'
Apologies if you have already tried this but to my knowledge, far from an expert on BCP, the above example will override the Row Terminator will be nothing, hence no dodgy line break in the middle of element tags.
I have faced a similar issue, this happens because of he size of the column_width
size of the results being returned, I think he default width is 2034, you can use the SQLCMD command and set -w property to overcome this.
This link might be helpful, http://connect.microsoft.com/SQLServer/feedback/details/786004/sqlcmd-with-xml-on-break-lines-on-large-output
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With