SQL Server Bcp Export XML Format

Question

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

Answer 1

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.

Answer 2

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