#0349 – SQL Server – SSMS Usability – Unable to Show XML, Unexpected End of File when parsing

Today’s post comes from a recent experience. I was trying to extract an XML from one of the logs that we maintain in the application when exchanging data over an interface. The XML was coming from a table and I had written a simple SELECT statement similar to the one shown below:


While SSMS displayed the XML in the results pane, clicking on the XML attempts to open the XML. With a very large XML, it results into the following error:


TITLE: Microsoft SQL Server Management Studio

Unable to show XML. The following error happened:
Unexpected end of file while parsing Name has occurred. 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.


The solution

The solution is actually indicated in the error message itself – increase the number of characters being retrieved from the server for XML data. To do this:

  1. Go to Tools –> Options
  2. Expand the “Query Results” node
  3. Expand SQL Server –> Results to Grid options
  4. Under “Maximum Characters Retrieved”, select the appropriate value for “XML Data”
  5. Click “OK” and close out of the Options window
  6. Attempt to open the XML again


A note of caution

Changing the Maximum Characters Retrieved value may impact the SSMS performance depending upon the size of the XML being opened.

Until we meet next time,

Be courteous. Drive responsibly.


Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.