Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 1) - XQuery Defined

Storing XML in the database is one thing; querying it efficiently is another. With SQL Server 2000, which has no XML data type, you have to deconstruct the XML and move element and attribute data into relational columns to perform a query on the XML data residing in the text column, or else you must use some other searching mechanism, such as full-text search.

XQuery Defined

XQuery is a language used to query and process XML data. It is a W3C standard whose specification is located at http://www.w3.org/TR/xquery/. The XQuery specification contains several descriptions of requirements, use cases, and data models. We encourage you to go to the specification and read “XQuery 1.0, an XML Query Language” and “XQuery 1.0 and XPath 2.0 Functions and Operators” to get a full understanding of what XQuery is all about. However, we will explain enough here for you to get the basics done. After reading this section, you will be able to select, filter, and update XML data using XQuery.

Understanding XQuery Expressions and XPath

The first thing to remember is that XQuery is an XML language, so all the rules of XML apply: It is case sensitive and its keywords are all in lowercase. While XQuery has some powerful formatting and processing commands, it is primarily a query language (as its name suggests), so we will focus on writing queries. The body of a query consists of two parts: an XPath expression and a FLWOR expression. Let’s start by discussing XPath expressions.

XPath 2.0

XPath, another W3C standard (http://www.w3.org/TR/xpath), uses path expressions to identify specific nodes in an XML document. These path expressions are similar to the syntax you see when you work with a computer file system (for example, c:\folder\myfile.doc). Take a look at the following XML document:

<catalog>
  <book category="ITPro">
    <title>Windows Step By Step</title>
    <author>Bill Zack</author>
    <price>49.99</price>
  </book>
  <book category="Developer">
    <title>Developing ADO .NET</title>
    <author>Andrew Brust</author>
    <price>39.93</price>
  </book>
  <book category="ITPro">
    <title>Windows Cluster Server</title>
    <author>Stephen Forte</author>
    <price>59.99</price>
  </book>
</catalog>

The following XPath expression selects the root element catalog:

/catalog

The following XPath expression selects all the book elements of the catalog root element:

/catalog/book

And this XPath expression selects all the author elements of all the book elements of the catalog root element:

/catalog/book/author

One additional note: If the path starts with a slash (/), it represents an absolute path to an element. To sum up, XPath enables you to specify a subset of data within the XML (via its location within the XML structure) with which you want to work. Using XPath gets you to the data you want to process, and it is essentially a very basic form of querying against XML data because it allows you to select a subset of data. XQuery is more robust and allows you to perform more complex queries against the XML data via FLOWR expressions.

Selection Logic: FLWOR Expressions

Just as SELECT, FROM, WHERE, GROUP BY, and ORDER BY form the basis of SQL’s selection logic, the for, let, where, order by, and return (FLWOR) keywords form the basis of every XQuery query you write. You use the for and let keywords to assign variables and iterate through the data within the context of the XQuery. (The let keyword is not supported in the SQL Server 2005 implementation of XQuery.) The where keyword works as a restriction and outputs the value of the variable. For example, the following basic XQuery uses the XPath expression /catalog/book to obtain a reference to all the <book> nodes, and the for keyword initiates a loop, but only of elements where the category attribute is equal to “ITPro”. This simple code snippet iterates through each /catalog/book node using the $bfor statement only where the category attribute is “ITPro” and returns as output the resulting information ordered by the author’s name using the order keyword. variable with the

for $b in /catalog/book
where $b/@category="ITPro"
   order by $b/author[1] descending
   return ($b)

Armed with this basic knowledge of XQuery expressions, you are ready to see it in action. Here is a simple example that uses this XQuery expression on an XML data type variable. We assign the XML to the variable and then use the preceding XQuery expression in the query() method (explained in the next section) of the XML data type.

DECLARE @XML xml
Set @XML='<catalog>
  <book category="ITPro">
    <title>Windows Step By Step</title>
    <author>Bill Zack</author>
    <price>49.99</price>
  </book>
  <book category="Developer">
    <title>Developing ADO .NET</title>
    <author>Andrew Brust</author>
    <price>39.93</price>
  </book>
  <book category="ITPro">
    <title>Windows Cluster Server</title>
    <author>Stephen Forte</author>
    <price>59.99</price>
  </book>
</catalog>
'
Select @XML.query('for $b in /catalog/book
   where $b/@category="ITPro"
   order by $b/author[1] descending
   return ($b)')

					  

The results are as follows. Notice that Stephen’s record is first because our order is descending by the author element. Andrew’s record is not in the output because we are restricting only for “ITPro” in the category element.

<book category="ITPro">
  <title>Windows Cluster Server</title>
  <author>Stephen Forte</author>
  <price>59.99</price>
</book>
<book category="ITPro">
  <title>Windows Step By Step</title>
  <author>Bill Zack</author>
  <price>49.99</price>
</book>

Now that you have seen the basics, let’s look at using XQuery with our SQL Server data.