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:

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

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.