Why does a carriage-return character disappear when converting a string to SQL Server XML datatype?
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. Doe开发者_运维百科s 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,

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 
 after you've copied and pasted the code
SET @var = CONVERT (XML,'<root>Dear Sir,
I am passing CR</root>',1)
SELECT @var
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 

, it will displays two whitespace.
精彩评论