Managing SQL Server XML Result Sets with XML Notepad 2007

By:   |   Comments   |   Related: > XML


Problem

With SQL Server 2005's native support of XML, XML seems to be popping up more and more in applications and as output from a variety of SQL Server administrative commands.  In particular organizations, working with XML has become the norm for particular needs and applications.  Unfortunately, creating, managing and editing XML data has still been a challenge with the native SQL Server tool set.  Although a number of the native SQL Server commands (Cross Walk - SQL Server Query Plans) can have data returned in an XML format, working with the data from that point forward can be a major challenge, especially with large result sets.  As such, how should I go about working with XML result sets directly from SQL Server 2005?

Solution

One approach is to download a free tool from Microsoft called the XML Notepad 2007.  What you can do with this tool is capture the needed information from SQL Server in XML format and then be able to work with the data in the XML Notepad 2007 tool.  This tool provides the following functionality:

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

So let's walk through some examples to show how this tool can be helpful when managing XML result sets from a SQL Server perspective.  These examples should show how using the XML Notepad 2007 saves time and allows you to read the SET SHOWPLAN XML and SET STATISTICS XML result sets in a more readable format than with SQL Server 2005 Management Studio.

Example 1 - SET SHOWPLAN_XML

As a first example, let's capture the query plan when running SHOWPLAN_XML in SQL Server 2005 Management Studio using the AdventureWorks database.

SET SHOWPLAN_XML ON
GO
SELECT *
FROM Person.Contact
GO

Once you execute this query you will see the following results shown below:

XML2

Once you click the link from the result set window in SQL Server 2005 Management Studio, it will display the XML as shown below.  This data is invaluable, but is there a better way to view this data?  Here is where Microsoft's XML Notepad 2007 comes in handy.

SHOWPLAN XML

The XML PLAN can be saved in my test directory as "Test XML Showplan2.xml". Now it is possible to open this file using Microsoft's XML Notepad 2007 as shown below.  As you can see, the results are in a more readable format than above in SQL Server 2005 Management Studio. In addition, each of the nodes can be expanded or collapse on the left side to see only the parts of the query plan in order to focus on the issue.

XMLNotePad2007 XMLSHOWPLAN

Example 2 - SET STATISTICS XML

As a second example, let's capture the actual plan by using SET STATISTICS XML ON.  The SET STATISTICS XML ON example shown below provides more information in the query plan such as the degree of parallelism, compile time, etc.

SET STATISTICS XML ON
GO
SELECT *
FROM Person.Contact PC
INNER JOIN Person.Address PA
ON PC.ContactID = PA.AddressID
GO

Once you click the link, you can save the XML file to a local directory just like in the previous example.  After opening the file in Microsoft's XML Notepad 2007 you will have something like this:

XMLNotePad2007 SETSTATISTICSXML

 

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jay Dave Jay Dave has over 10 years of experience in the IT industry working as a trainer, software developer, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article

















get free sql tips
agree to terms