Error Fix: Unable to show XML The following error happened There is an unclosed literal string
Recently I was working with huge data of XML in SQL Server. One of the row having XML string with approx 98000 rows inside. If I try to open that XML file by clicking on XML value return by my SELECT query, I used to get following error:
Unable to show XML. The following error happened:
There is an unclosed literal string. Line 1, position 2097154.
One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.
Here is the screen capture of error:
This error comes because of limitation set by default of 2MB for XML data. My data for that XML field was having approx 18MB. To open this big file, I have to change the SSMS configuration from Tools | Options.
You can see that “Results to Grid” has option to retrieve maximum characters. For “Non XML Data:”, it is 65535 and for XML data, it is 2MB. My data was around 18MB so obviously I can’t get it open in SSMS. I have changed (temporary) it to “Unlimited” for a while, clicked on “OK” button. Close my current query window and execute query again. I got my XML field in SSMS and when I have clicked on that field, I got my XML file open.
If you like this article, do like “Extreme-Advice” page in Facebook.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.