SQL Server loses carriage return when casting to and then back from XML-CodePudding

If I convert a nvarchar string containing a full line break (\r\n) to XML, then when converting back, I find that instead of \r\n the resulting string contains only \n.

Is there a server or connection option controlling this behaviour?

declare 
  @ln nvarchar(2) = char(13) char(10)

declare 
  @str nvarchar(max) = '<x>qwe ' @ln ' asd</x>'
print @str
print charindex(@ln, @str) -- found
/*  output: 8 */

-- convert to xml
declare @xml xml = convert(xml, @str)
-- convert back to str
set @str = convert(nvarchar(max), @xml)

print @str
print charindex(@ln, @str) -- not found
/*  output: 0 */
print charindex(char(13), @str) -- not found
/*  output: 0 */
print charindex(char(10), @str) -- found
/*  output: 8 */

CodePudding user response：

This behavior is by design.

From Extensible Markup Language (XML) 1.0 (Fifth Edition), 2.11 End-of-Line Handling:

XML parsed entities are often stored in computer files which, for editing convenience, are organized into lines. These lines are typically separated by some combination of the characters CARRIAGE RETURN (#xD) and LINE FEED (#xA).

To simplify the tasks of applications, the XML processor must behave as if it normalized all line breaks in external parsed entities (including the document entity) on input, before parsing, by translating both the two-character sequence #xD #xA and any #xD that is not followed by #xA to a single #xA character.

If you want to preserve carriage return characters in text you typically need to convert them to entity encoding such as  or .