Export SQL Server view to XML inserts undesired CRLF every 2034 characters

Question

I am using the following SP to export a view to a file:

SET NOCOUNT ON;
declare @line varchar(255)
set @line = 'bcp "SELECT * FROM erpSqlBe.dbo.vwPriceListStockxx for xml , root(''daily'')" queryout "d:\myFolder\Daily.xml" -c -CACP -Uyyy -Pzzz '
SELECT @line
EXEC xp_cmdshell @line

The resulting file has a CRLF inserted every 2034 characters, making the file unreadable. What is the fix for this ?
I found this answer, but I don't know how to apply it here.
Thanks

score 1 · Accepted Answer · answered Nov 19 '15 at 12:10

If I understand this correctly, the solution you're trying to apply is converting the XML data returned by for xml to varchar(max). This can be done using CAST({xml_data} AS VARCHAR(MAX)) like so :

set @line = 'bcp "SELECT CAST((SELECT * FROM erpSqlBe.dbo.vwPriceListStockxx for xml , root(''daily'')) AS VARCHAR(MAX))" queryout "d:\myFolder\Daily.xml" -c -CACP -Uyyy -Pzzz '

or using CONVERT(VARCHAR(MAX), {xml_data}) :

set @line = 'bcp "SELECT CONVERT(VARCHAR(MAX), (SELECT * FROM erpSqlBe.dbo.vwPriceListStockxx for xml , root(''daily'')))" queryout "d:\myFolder\Daily.xml" -c -CACP -Uyyy -Pzzz '

Export SQL Server view to XML inserts undesired CRLF every 2034 characters

1 Answers1