The carriage return character is removed from XML using OPENXML

Question

It appears that SQL Server removes the \r characters when parsing XML. So when my stored procedure gets the values ​​in xml to save, all line breaks are represented as \n instead of \r\n.

Is there a way to force SQL Server not to remove \r characters? In the example Node1 below, the value does not contain \r characters.

 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 XQuery nodes instead. But that does not help. I tried to execute the request 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 1

The carriage return character is removed from XML

This is the correct behavior according to the XML specification End of Line Processing .

the XML processor MUST behave as if it normalizes all line breaks to external parsed objects (including the document object) at the input, before parsing, by translating the two-character sequence #xD #xA and any #xD that is not followed by #xA, to a single #xA character.

You can try using replace to return a carriage return.

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