question

dw.bi avatar image
dw.bi asked

How to extact XML data from XML stored in NTEXT data type field in a SQL Server table?

Hi there, I have large XML content (100,000 characters) stored in NTEXT data type fields in an SQL table. I am trying to store the xml content into a different table with a column of XML data type, so that I can use XQuery to extract node data. But I get the following error: **XML parsing: line 1, character 4000, unexpected end of input** I think it is because the XML is getting truncated. Please help. When I do an explicit cast of the ntext to xml I get this error: SELECT CAST(XMLSTRING AS XML) FROM xmltable WHERE id =1 *XML parsing: line 1, character 39, unable to switch the encoding* When I do two casts from ntext to text and then to xml it gets slightly better: SELECT CAST(CAST(XMLSTRING AS TEXT) AS XML) FROM xmltable WHERE id =1 *XML parsing: line 1, character 46887, illegal xml character* I have attached screenshots of my table layout and sample XML.![alt text][1] [1]: /storage/temp/638-xmltable.png
sqlxmlntext
samplexml.png (16.3 KiB)
xmltable.png (15.8 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
OK so the first error (you don't show the query you are using), is truncating the string at 4000 chars, is this because you are using a function of some sort, so yes you are assuming right. The second query gets the error `XML parsing: line 1, character 39, unable to switch the encoding` is because SQL Server stores XML as UTF-16, and looking at your screenshot, your XML is encoded with UTF-8. The second query, as you rightly put it, is getting closer....can you check what you've got at character 46887? It should be as simple as that.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much for your reply. The answer to your 2nd question: There is no character at 46887 because the xml ends at 46886. datalength(xmldata) = 96,000 BUT The xml ends at 46886.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.