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.
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:
Go to Tools –> Options
Expand the “Query Results” node
Expand SQL Server –> Results to Grid options
Under “Maximum Characters Retrieved”, select the appropriate value for “XML Data”
Click “OK” and close out of the Options window
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,