#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:

SELECT xt.XMLTest
FROM dbo.XMLSSMSTest AS xt;
GO

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:

image

——————————
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.

——————————
BUTTONS: OK
——————————

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

image

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.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s