Shred XML data with XQuery in SQL Server 2005

Tim Chapman discusses new functionality in SQL Server 2005 that allows you to shred XML data into a relational format without the intensive memory operations. He also explains how using XQuery rather than OPENQUERY can lead to performance gains.

Last week, I outlined how to shred XML data into a relational format by using the OPENXML function in SQL Server. We learned that OPENXML is straightforward and simple to use, yet very memory intensive. This week, I discuss new functionality in SQL Server 2005 that allows you to shred XML data into a relational format without the intensive memory operations.

Before I present the examples used for shredding data in my previous article, let's take a look at XQuery and the functionality it offers developers in SQL Server 2005.

What is XQuery?

XQuery, also known as XML Query, is a language designed to query XML data, allowing you to extract nodes and elements as needed. It is defined by the W3C, and is available for use in today's most popular database engines, such as Oracle, DB2, and SQL Server.

SQL Server 2005 XQuery functions

The following four functions are the XQuery functions available in SQL Server 2005. (Note that XML is case sensitive and so are XQuery statements, including the following functions. For example, the SQL compiler will allow .exist on XML data, but will reject .EXIST or .Exist.)

xml.exist
This method returns a Boolean value based upon a search expression on an XML node. For example, given the XML snippet in Listing A, this statement will return 1 (TRUE):

    SELECT @x.exist('/christmaslist/person[@gift = "socks"]')

this statement will return 0 (false):

    SELECT @x. exist ('/christmaslist/zach') 

and this statement will return 0 (false) because of the casing of the word "Socks".

    SELECT @x.exist('/christmaslist/person[@gift = "Socks"]')

xml.value
This method accepts an XQuery statement and returns a single value. By using the same XML snippet from Listing A, I can use the VALUE function to produce the following, which yields the value "betty":

SELECT @x.value('/christmaslist[1]/person[1]/@name', 'VARCHAR(20)') 

while the XQuery yields the value "zach".

SELECT @x.value('/christmaslist[1]/person[2]/@name', 'VARCHAR(20)') 

xml.query
This method accepts an XQuery statement and returns an instance of the XML data type. These queries can be as simple or as complex as necessary. For the sake of simplicity, here is an easy example:

SELECT @x.query('/christmaslist/person') 

which returns the XML document:

<person name="betty" gift="camera" />
<person name="zach" gift="elmo doll" />
<person name="brad" gift="socks" />

xml.nodes
This method is very useful when you need to shred the data from an XML data type variable into relational data. This method accepts an XQuery statement as a parameter and returns a rowset that contains logical scalar data from the XML variable. The query in Listing B takes the XML variable defined above, and shreds the data into a resultset that displays the name of the person defined in the XML variable.

Modify the OPENXML stored procedure

Now I'll show you how to modify the OPENXML stored procedure from last week to use XQuery functionality instead. First, I'll load some data into an XML variable. View Listing C. We can create a procedure that accepts an XML parameter, and uses XQuery functions to insert data from the XML file into a table, rather than using OPENXML. View Listing D.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

It may seem awkward at first to use XML in the database, and it can take some time to get used to using XQuery and XPath queries. However, once you are able to get over the initial XML learning curve, you will begin to see how useful it can be to use XML data in the database.

For example, by using XML data in the stored procedure above, I am making one database call rather than N number of database calls, which is the norm with stored procedure programming. This may seem like a small difference, but it can yield huge benefits on a very busy system. Also, using XQuery rather than OPENQUERY will increase your performance gain even more, especially with smaller-sized XML documents.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

By Tim Chapman

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.