Querying XML and Relational Data Using XQuery

XQUERY is a language designed to query XML data. It is a full programming language that is used to execute complex extractions, joins, and filters around your XML data. SQL Server 2005 is aligned with the July 2004 working draft of XQUERY and supports a subset of this query language. The query() method of the xml datatype provides the ability to execute an XPATH or XQUERY expression, and it returns the resulting XML fragment.

Using XQUERY in Queries

1. Load the XML data into an xml datatype variable, as in the previous procedure. The code in this section can be be accessed from \Ch07\Samples08.sql in the sample files.

    DECLARE @MYDATA XML SET @MYDATA = '<Vendors>   <Vendor VendorID="1" AccountNumber="INTERNAT0001" Name="International">     <Product ProductID="462" Name="Lower Head Race" />   </Vendor>   <Vendor VendorID="2" AccountNumber="ELECTRON0002"    Name="Electronic Bike Repair Supplies">     <Product ProductID="511" Name="ML Road Rim" />     <Product ProductID="510" Name="LL Road Rim" />     <Product ProductID="512" Name="HL Road Rim" />   </Vendor>   <Vendor VendorID="3" AccountNumber="PREMIER0001" Name="Premier Sport, Inc.">     <Product ProductID="513" Name="Touring Rim" /> 

    </Vendor>   <Vendor VendorID="4" AccountNumber="COMFORT0001" Name="Comfort Road Bicycles">     <Product ProductID="507" Name="LL Mountain Rim" />     <Product ProductID="508" Name="ML Mountain Rim" />   </Vendor>   <Vendor VendorID="5" AccountNumber="METROSP0001" Name="Metro Sport Equipment">     <Product ProductID="528" Name="Seat Lug" />   </Vendor>   <Vendor VendorID="6" AccountNumber="GREENLA0001" Name="Green Lake Bike Company">     <Product />   </Vendor> </Vendors>' 

2. Construct a query using the query() method to extract specific sections of the XML data.

    SELECT @MYDATA.query('//Product[@ProductID>510]') 

   This simple XQUERY expression indicates that you would like to traverse the complete XML tree (//) and extract the Product elements that contain a ProductID attribute with a value larger than 510.

3. When executing this code, the result should look like the following:

    <Product ProductID="511" Name="ML Road Rim" /> <Product ProductID="512" Name="HL Road Rim" /> <Product ProductID="513" Name="Touring Rim" /> <Product ProductID="528" Name="Seat Lug" /> 

Another way of writing this same query would be:

 SELECT @MYDATA.query('     for $product in //Product     where data($product/@ProductID) > 510     return         <result>             {$product}         </result>     ') 

This is also an XQUERY expression, which is called a FLWOR (for, let, where, order by, return) expression. The result of executing this type of query should resemble the following:

 <result>   <Product ProductID="511" Name="ML Road Rim" /> </result> <result>   <Product ProductID="512" Name="HL Road Rim" /> </result> <result>   <Product ProductID="513" Name="Touring Rim" /> </result> <result>   <Product ProductID="528" Name="Seat Lug" /> </result> 

The XQUERY language supports encapsulating programming logic as functions. The data() function (used in the previous example) is part of the XQUERY language and extracts the values out of XML elements.

Note 

XQUERY is a complete programming language for which a thorough explanation is beyond the scope of this book. A recommended starting point for more information about XQUERY is to read the subtopics under the section, XQuery Against the xml Data Type, in SQL Server Books Online.