0

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

cxc
  • 201
  • 2
  • 10
  • Change `''+Char(13)+Char(10)` to `CAST('' AS VARCHAR(MAX)) +Char(13)+Char(10)` so the initial thing you are concatenating has MAX datatype – Martin Smith Apr 30 '20 at 18:59
  • @Martin Smith haha yeah just found that answer as well. Thanks! – cxc Apr 30 '20 at 19:01

1 Answers1

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';
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
  • Hmm this is not the main problem I have. I did actually found the issue. It's the limit of string concatenation. Still not sure how to solve it – cxc Apr 30 '20 at 18:51
  • You are still dealing with formatting of XML... What is the source of the XML? – Yitzhak Khabinsky Apr 30 '20 at 19:02