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] : /storage/temp/638-xmltable.png
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.