1. Understanding XQuery Expressions and XPath
XQuery is a language used to query and process XML data. XQuery is a W3C standard, and its 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 review the
specification to get a full understanding of what XQuery is all about.
For now, we will explain enough to cover the basics. After reading this
section, you will be able to select, filter, and update XML data using
XQuery.
Because
XQuery is an XML language, all the rules of XML apply. XQuery uses
lowercase element names (“keywords”), and because XML itself is
case-sensitive, you must take this into account when writing queries.
Although XQuery has some powerful formatting and processing commands, it
is primarily a query language (as its name suggests), so we will focus
here on writing queries. The body of a query consists of two parts: an
XPath expression and a FLWOR (pronounced “flower”) expression. (FLWOR is an acronym based on the primitive XQuery keywords for, let, where, order by, and return.)
XPath, another W3C standard (http://www.w3.org/TR/xpath), uses path expressions to identify specific nodes and attributes 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>Jeff Hay</author>
<price>49.99</price>
</book>
<book category="Developer">
<title>Learning ADO .NET</title>
<author>Holly Holt</author>
<price>39.93</price>
</book>
<book category="ITPro">
<title>Administering IIS</title>
<author>Jed Brown</author>
<price>59.99</price>
</book>
</catalog>
The following XPath expression selects the root element catalog:
/catalog
This 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
XPath enables you to specify a subset of data within the XML (via its location within the XML structure) that you want to work with. XQuery is more robust and allows you to perform more complex queries against the XML data using FLWOR expressions combined with XPath.
Just as SELECT, FROM, WHERE, GROUP BY, and ORDER BY form the basis of the SQL 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 query. The where keyword works as a restriction and outputs the value of the variable.
For example, the following basic XQuery query 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 $b variable with the for statement only where the category attribute is “ITPro” and then returns as output the resulting information in descending order by the author’s name using the order keyword:
for $b in /catalog/book
where $b/@category="ITPro"
order by $b/author[1] descending
return ($b)
Example 1 shows a simple example that uses this XQuery expression on an xml data type variable. XML is assigned to the variable, and then the preceding XQuery expression is used in the query method (explained in the next section) of the xml data type.
Example 1. A simple XQuery example.
DECLARE @Books xml = '
<catalog>
<book category="ITPro">
<title>Windows Step By Step</title>
<author>Jeff Hay</author>
<price>49.99</price>
</book>
<book category="Developer">
<title>Learning ADO .NET</title>
<author>Holly Holt</author>
<price>39.93</price>
</book>
<book category="ITPro">
<title>Administering IIS</title>
<author>Ted Bremer</author>
<price>59.99</price>
</book>
</catalog>'
SELECT @Books.query('
<ITProBooks>
{
for $b in /catalog/book
where $b/@category="ITPro"
order by $b/author[1] descending
return ($b)
}
</ITProBooks>')
The results are as follows:
<ITProBooks>
<book category="ITPro">
<title>Administering IIS</title>
<author>Ted Bremer</author>
<price>59.99</price>
</book>
<book category="ITPro">
<title>Windows Step By Step</title>
<author>Jeff Hay</author>
<price>49.99</price>
</book>
</ITProBooks>
Notice that Ted’s record is first because the order is descending by the author element. Holly’s record is not in the output because the category element is restricted to “ITPro”. There is a root element wrapped around the XQuery statement with <ITProBooks> and </ITProBooks>, so all the results for IT books extracted from source XML having a catalog root element are contained inside of an ITProBooks root element.