views:

209

answers:

1

Whenever I parse a string to SQL XML datatype I notice that carriage-returns disappear whenever whitespace and XML carriage returns are mixed. If they are not mixed things work fine. Does anyone know why this is happening?

See the SQL example code below

DECLARE @var XML

PRINT 'This is a statement with two XML carriage-return characters'
SET @var = CONVERT (XML,'<root>Dear Sir,&#xD;&#xD;I am passing CR</root>',1)
SELECT @var

PRINT 'output is identical to a statement with two whitespace carriage-return characters'
SET @var = CONVERT (XML,'<root>Dear Sir,

I am passing CR</root>',1)
SELECT @var

PRINT 'Why does this statement only display one space? There is an XML carriage-return AND a whitespace carriage-return character.' 

--Make sure there is no space after &#xD; after you've copied and pasted the code 
SET @var = CONVERT (XML,'<root>Dear Sir,&#xD;
I am passing CR</root>',1)
SELECT @var
+2  A: 

In Windows, end of line is CR-LF. In Unix, end of line is CR.

In the first example, you use CR-CR. I guess SQL Server interprets those as Unix style line endings, giving two whitespace.

You second example was typed in Windows, giving CR-LF-CR-LF. This is interpreted as Windows style line endings, giving two whitespace.

Your third example is CR-CR-LF. That's apparently interpreted as Windows style line endings, giving one whitespace and an unmatched CR.

If you change your third example to use CR-LF, or &#xD;&#xA;, it will displays two whitespace.

Andomar