SQLServerCentral Article

XML Notepad with SSMS


There are many XML editors out there, but the one I use is from Microsoft. The reason I use Microsoft XML Notepad is to see my SQL Server 2005 XML Query Plans.

I ran the following SQL Statements in SSMS using the AdventureWorks database.

Once you execute this query you will see following result shown below

Once you click the link it will display as shown below, and you can see that this is what I don't want to read. It is, however, invaluable to me so what is a better way to view this data? Here is where Microsoft's XML Notepad 2007 comes in handy.

I can save this XML PLAN in my test directory as "Test XML Showplan2.xml". Now I open this file using Microsoft XML Notepad 2007 and you will see the result below, which is in a more readable format then that shown above in SSMS. You can expand or collapse each node on the left side to see only the parts of the plan you are interested in.

You can also do the same to get actual plan by using SET STATISTICS XML ON, see the example shown below which gives more information about Query plan like degree of parallelism, compile time etc...........

Once you click the link, you can save the XML file to a local directory. After opening it in XML Notepad you will something like that shown below.

Using Microsoft XML NOTEPAD 2007 saves time and allows you to read the Showplan & Statistics XML data in a more readable format than shown in SSMS.

Some of the useful features of XML Notepad include:

  • View XML as text, tree or spreadsheet-like grid
  • Cut/copy/paste copies syntax coloring
  • Drag & drop editing supported
  • Blocks of XML can be expanded and collapsed
  • Infinite undo/redo, even for major operations like re-indenting file or making XML canonical
  • Supports huge documents over 100 megabytes in size
  • Nudge buttons for moving nodes up and down the tree view
  • Preview window for xml-style sheet processing instruction
  • XML Differencing tool supports single or multiple documents
  • Project window to group related files