By: Jay Dave | 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:
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
- As XML is used more in applications, traditional management tools are going to be a little cumbersome, so relying on a separate tool like XML Notepad 2007 may be a good alternative when you need to review, edit and create XML documents. The traditional management tools may change to meet those needs, but until then using XML Notepad 2007 may be your best alternative.
- This tip has only scratched the surface of the XML Notepad 2007 capabilities. Check out all that the tool has to offer and consider it a viable utility in your tool belt as you work with XML more and more from an administrative and development perspective.
- To download the XML Notepad 2007, please visit this URL:
- Check out the XML tips on MSSQLTips.com for more information on managing XML with SQL Server 2005:
- Special thanks to Jay Dave of the MSSQLTips.com community for contributing this tip.
About the author
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
