Error Fix: Unable to show XML The following error happened There is an unclosed literal string

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:

XMLError

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.

XMLErrorFix

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

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

3 thoughts on “Error Fix: Unable to show XML The following error happened There is an unclosed literal string”

Comments are closed.