Shortcut: XML editor in SSMS and increasing XML output size

Most people use SQL Server Management Studio (SSMS) to edit SQL queries. No big surprise there. The files will have a file type of .sql.

But what many people don’t understand is that SSMS inherits many of its underlying Visual Studio’s abilities to edit other document types.

For example, if you open a .txt text file, you can edit it just fine, and you can also include files like this in SSMS script projects. That can be useful for additional notes and documentation.

SSMS also knows how to open related file types like .sqlplan (for query plans) and .xdl files (for deadlocks), and more.

Most of these other file types though, are actually XML files with specific schemas constraining their contents. SSMS also contains a perfectly acceptable XML editor.

Here’s an example:

If I execute the above query, the outcome is some XML. Note that SSMS recognizes that the output data type is XML and then provides a hyperlink for opening it. If I click on the link, I see this:

The important item to notice here though is the red squiggly on the second line. If we hover over that, we’ll see this:

XML documents can only have a single root element. This XML is actually a fragment, not a complete document, and so it thinks that all the stock item lines are all root elements.

The important thing is that this is an XML editor, not just an XML viewer. Notice that when an XML file is open, an XML menu also appears:

Now, while it’s not a bad XML editor, it has limits on the size of the data that you can work with, but you can control that too. In Tools, Options, Query Results, SQL Server, Results to Grid, you can see this:

By default, you are limited to 2MB of XML data. You can increase this to unlimited but keep in mind that SSMS is (unfortunately) still a 32 bit application and can struggle to work with gigantic files.

2018-10-04