Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Managing SQL Server XML Result Sets with XML Notepad 2007


By:   |   Last Updated: 2007-06-22   |   Comments   |   Related Tips: More > 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:

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.

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.

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:

 

Next Steps



Last Updated: 2007-06-22


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools