0

I have a table which I need to export to a csv file. This table has an xml field which can have line breaks that I must remove. I am using the bcp utility to export data from Sql Server to the csv file.

This is the strucutre of the table:

ID int
CODE varchar(20)
XML_DATA xml

This is the command I am using:

bcp "SELECT ID, CODE, replace(convert(nvarchar(max), XML_DATA), CHAR(13)+CHAR(10), ' ') as XML_DATA FROM MYDB.dbo.MyTable" queryout C:\output.csv -c -t0x1F -T -S "10.10.10.28"

For some reasons the output file still contains the line breaks. But if I run the same query on SQL Server for a record which has a line break, the output is correct (line breaks removed):

SELECT
    ID, 
    CODE, 
    replace(convert(nvarchar(max), XML_DATA), CHAR(13)+CHAR(10), ' ') as XML_DATA
FROM MYDB.dbo.MyTable
WHERE ID = 1099; -- record with a line break in the XML_DATA field

What am I missing here?

revy
  • 3,945
  • 7
  • 40
  • 85
  • 1
    It may be that your xml contains CHAR(10) + CHAR(13), not CHAR(13)+CHAR(10), and when you run the query in SSMS you don't see that line breake is still there because you may output to grid, you should output to Text instead – sepupic Apr 02 '19 at 10:44
  • @sepupic Yep I was writing exactly that. Thanks! – revy Apr 02 '19 at 10:46

1 Answers1

2

Solved replacing this line

replace(convert(nvarchar(max), XML_DATA), CHAR(13)+CHAR(10), ' ') as XML_DATA

with this

replace(replace(convert(nvarchar(max), XML_DATA), CHAR(13), ' '), CHAR(10), ' ') as XML_DATA

Also I would like to point out that Sql Server Management Studio by default does not include CLRF characters on copy/paste from a query result, you must explicitly enable an option to preserve CLRF characters and restart SQL Server: preverse CLRF on Sql Server Management Studio query result. That was giving me some confusion ^_^

revy
  • 3,945
  • 7
  • 40
  • 85
  • >>>you must explicitly enable an option to preserve CLRF characters<<< As already mentioned in comment, you can simply ouput to text instead of a grid, you can find this button on a toolbar in SSMS next to execution plan button, there are 3 similar buttons with an arrow that put data to text(you'll see rows on the picture), to grid(default choice), to file (blank sheet) – sepupic Apr 02 '19 at 10:53