XML input string got cut off when using convert() in sql

Question

I am trying to update an item in the table with following code:

UPDATE My_Table SET My_Xml = CONVERT(XML, '<xml>MY_SUPER_LONG_XML_STRING</xml>') WHERE id = '001'; 

The XML string I have is around 100 lines long. I understand that this way CONVERT() will remove all the carriage returns and line feeds. So if I set STYLE in the CONVERT() to be "1" this would preserve all the white space. The following code here works for me perfectly:

UPDATE My_Table SET My_Xml = CONVERT(XML, '<xml>MY_SUPER_LONG_XML_STRING</xml>', 1) WHERE id = '001'; 

The problem is this will go through some other system and I have to use ascii characters to replace carriage returns and line feeds so I changed it to be like this:

UPDATE My_Table SET My_Xml = CONVERT(XML, 
'<xml>'+Char(13)+Char(10)+
'MY_SUPER_LONG_XML_STRING_LINE1'+Char(13)+Char(10)+
'MY_SUPER_LONG_XML_STRING_LINE2'+Char(13)+Char(10)+
...
'</xml>') WHERE id = '001'; 

This works ONLY when the XML input string is around 30 lines long. When I use the original string I get an error:

Msg 9400, Level 16, State 1, Line 5
XML parsing: line 28, character 126, unexpected end of input

which seems like the input string got cut off. I mean a couple hundred lines of xml is still not long long considering the limit for xml in sql is 2 GB. Also the way I set the STYLE flag to be 1 worked for me with xml of any length. So ideally replacing "\r\n" with Char(13)+Char(10) should work as well, right? Any idea why this is happening?

UPDATE: Just found out what the problem is. There seems to be a limit for string concatenation in sql server. Still not sure how to solve it

Answer 1

SQL Server stores internally XML data type as UTF-16 binary format. It is not a string with arbitrary Line Feed and Carriage Return characters.

When XML is persisted on the file system, it could be as a stream, or indented for a human eye. But in both cases it is still a legit XML.

"...The problem is this will go through some other system and I have to use ascii characters to replace carriage returns and line feeds...".

I guess that other system has some real issues while handling XML.

I would change your SQL as follows:

DECLARE @xml XML = N'<xml>MY_SUPER_LONG_XML_STRING</xml>';

UPDATE My_Table 
SET My_Xml = @xml 
WHERE id = '001';