Carriage return symbol is removed from XML using OPENXML

It looks like SQL Server removes r characters when parsing XML. So when my stored procedure receives values in xml for saving, all line breaks are represented as n instead of rn.

Is there any way I can force SQL Server to not remove r characters? In the sample below Node1 value doesn’t contain r symbols.

    DECLARE @hDoc int
    DECLARE @Xml nvarchar(MAX) 
    SET @Xml = N'<Root><Node1><![CDATA[' + nchar(13) + nchar(10) + N'Some ' + nchar(13) + nchar(10) + N' Value]]></Node1></Root>'
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @Xml

    SELECT 
        Node1
        ,convert(varbinary(max),Node1) -- Contains 0A00 in the start instead of 0D0A,
        ,charindex(nchar(13),Node1)
    FROM
        OPENXML(@hDoc, N'/Root', 2) WITH (Node1 NVARCHAR(MAX))

    EXEC sp_xml_removedocument @hDoc

Output:

@PJB suggested to use XQuery nodes instead. But this doesn’t helps. I tried to run query below and got the same result.

    DECLARE @xml xml
    SET @xml = convert(xml, N'<Root><Node1><![CDATA[' + nchar(13) + nchar(10) + N'Some ' + nchar(13) + nchar(10) + N' Value]]></Node1></Root>')

    declare @Node1 nvarchar(30)
    select @Node1 = node.value('.', 'nvarchar(30)')
    from @xml.nodes('/Root/Node1') as doc(node)

    SELECT 
        @Node1
        ,convert(varbinary(max),@Node1) -- Contains 0A00 in the start instead of 0D0A,
        ,charindex(nchar(13),@Node1)

Answer

Carriage return symbol is removed from XML

This is the correct behavior according the XML spec on End-of-Line Handling.

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.

You could try to use a replace to get the carriage-return back.

select @Node1 = replace(node.value('.', 'nvarchar(30)'), nchar(10), nchar(13)+nchar(10))
from @xml.nodes('/Root/Node1') as doc(node)