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

Question

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 */

What are you actually trying to do here? This feels like an [XY Problem](//xyproblem.info). If, for example, you treat the XML as `xml` and the text as a `nvarchar`, the character remains: [db<>fiddle](https://dbfiddle.uk/Li6JgjMb) — Thom A, Dec 08 '22 at 16:41
For an XML is should not matter what kind of characters you have as line separators, as long as it's not used inside a field (inside the data that is stored in the XML). (Also see: https://stackoverflow.com/questions/2265966/xml-carriage-return-encoding#comment10606517_2266022 ) — Luuk, Dec 08 '22 at 16:47

score 2 · Accepted Answer · answered Dec 08 '22 at 21:14

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 .

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

1 Answers1