January 2008 Technical Tip
A Quick Look at XQuery
Introduction
The amount of data stored in XML documents is already substantial
and it is growing exponentially. Originally invented by publishers of
books and technical manuals, XML is now used extensively for a wide
range of information technology jobs. The most notable of these jobs
is XML-based Web services.
Wherever you find a collection of XML documents, you often find
a need to query these documents to support transaction processing,
to facilitate decision support, and to create Web pages and high quality
printed reports. XQuery is the new query language
standard that makes this kind of querying possible. XQuery plays
much the same role for XML documents as SQL plays for relational databases.
This article provides a series of code examples that show off some
of XQuery's capabilities. These examples are based on a small collection
of XML documents that describe the property listings of a fictitious
resort property rental agency in Colorado. Each XML document describes
a separate property. We start by extracting information from a single
propertyDescription document. Then, we move on to extracting information
from the entire collection of documents. Finally, we show off some of
the XQuery features that allow us to format our results into useful
forms like XHTML tables.
Querying a Single XML Document
The following example is the first of three propertyDescription
XML documents that we will query in the course of this article. It describes
a lavish estate that is available for rent:
<?xml version="1.0" encoding="UTF-8"?>
<!--
2001-256.xml
-->
<propertyDescription>
<propertyNumber>2001-256</propertyNumber>
<propertyName>Worthington Estate</propertyName>
<propertyClass>Multiple Dwelling</propertyClass>
<narrativeDescription>
This beautiful lakeside estate was formerly the summer home of the
financier E. B. Worthington. It is an ideal venue for corporate meetings,
weddings, and family reunions. Guests are housed in a combination of the
main house, two guest houses and the boat house. The atmosphere is elegant
and the accommodations are both modern and comfortable. Your guests will never
forget their visit to this late 19th Century masterpiece.
</narrativeDescription>
<propertyAddress>
<street>100 Worthington Lane</street>
<city>Mountain Forest</city>
<state>CO</state>
<zip>12345</zip>
</propertyAddress>
<capacity>
<sleeps>40</sleeps>
<seats>60</seats>
</capacity>
<terms>
<weekendTerms>
<rent>15000</rent>
<deposit>5000</deposit>
</weekendTerms>
<weeklyTerms>
<rent>25000</rent>
<deposit>10000</deposit>
</weeklyTerms>
<monthlyTerms>
<rent>75000</rent>
<deposit>25000</deposit>
</monthlyTerms>
</terms>
<owner>
<ownerName>Worthington Trust No. 43562</ownerName>
<ownerAddress>
<street>c/o Eastern Bank and Trust</street>
<street>1234 Broad Street</street>
<city>Monetary</city>
<state>DE</state>
<zip>55555</zip>
</ownerAddress>
<ownerContact>
<contactName>Winston Quillgate, Trust Administrator</contactName>
<contactPhone>555-555-1212</contactPhone>
</ownerContact>
</owner>
<features>
<feature>Wood-burning fireplace in each bedroom</feature>
<feature>Whirlpool baths in many bedrooms</feature>
<feature>Phone and color TV in each bedroom</feature>
<feature>Ballroom and meeting rooms in main house</feature>
<feature>Restaurant-grade kitchen in main house</feature>
<feature>Seating for 60 in two large meeting rooms</feature>
<feature>Indoor lap pool in main house</feature>
<feature>Sauna in main house</feature>
<feature>Kitchen and household staff available for additional fee</feature>
</features>
<activities>
<activity>Swimming at private beach and indoor lap pool</activity>
<activity>Boating from private boat house</activity>
<activity>Easy access to all Mountain Forest ski slopes</activity>
<activity>Easy access to three championship golf courses</activity>
</activities>
<restrictions>
<restriction>No tenants under 21 without parent or guardian</restriction>
<restriction>No open fires</restriction>
<restriction>No camping</restriction>
</restrictions>
</propertyDescription>
The preceding document is fed into an XQuery processor along with the following
simple query. Note the FOR and RETURN clauses in the query. The FOR clause iterates
over documents (in this case, just one document). The RETURN clause specifies the
results of the query.
(:
example_1.xquery
:)
for $prop in doc("2001-256.xml")/propertyDescription
return $prop/propertyName
The result of processing the query are presented below. Notice that the
result is an XML document. We have seleted a single XML element from the input
document and placed it into a new XML result document.
<?xml version="1.0" encoding="UTF-8"?>
<!--
example_1.xml
-->
<propertyName>Worthington Estate</propertyName>
Querying a Collection of XML Documents
While extracting data from a single XML document is interesting, this has
been possible for quite a while using the XSLT transformation language. Where
XQuery really shines is in querying multiple XML documents at once. This is
done by placing the XML documents into a collection. XQuery processors are
capable of processing collections of documents kept in the computer's file
system or kept in an XML-capable database. In this example, we are going to
process a collection of documents kept in the file system. The following
XML document combines our three propertyDescription documents into a
collection:
<?xml version="1.0" encoding="UTF-8"?>
<!--
rental_collection.xml
-->
<collection>
<doc href="2001-256.xml"/>
<doc href="2005-014.xml"/>
<doc href="2006-004.xml"/>
</collection>
The following propertyDescription document describes a modest apartment
near the Yellowbird ski resort. It is the second document in our
three-document collection:
<?xml version="1.0" encoding="UTF-8"?>
<!--
2005-014.xml
-->
<propertyDescription>
<propertyNumber>2005-014</propertyNumber>
<propertyName>2905 Yellowbird Apt. 4A</propertyName>
<propertyClass>Apartment</propertyClass>
<narrativeDescription>A charming, comfortable apartment within walking
distance of the Yellowbird Ski Resort. Spacious rooms with clean,
modern furnishings.</narrativeDescription>
<propertyAddress>
<street>2905 Yellowbird Lane</street>
<street>Apt. 4A</street>
<city>Mountain Forest Gorge</city>
<state>CO</state>
<zip>12345</zip>
</propertyAddress>
<capacity>
<sleeps>4</sleeps>
</capacity>
<terms>
<dailyTerms>
<rent>300</rent>
<deposit>150</deposit>
</dailyTerms>
<weekendTerms>
<rent>500</rent>
<deposit>250</deposit>
</weekendTerms>
<weeklyTerms>
<rent>2500</rent>
<deposit>1250</deposit>
</weeklyTerms>
</terms>
<owner>
<ownerName>Mary and John Fillmore</ownerName>
<ownerAddress>
<street>1445 Oak Street</street>
<city>Sometown</city>
<state>IL</state>
<zip>66666</zip>
</ownerAddress>
<ownerContact>
<contactName>Mary Fillmore</contactName>
<contactPhone>847-555-5555</contactPhone>
</ownerContact>
</owner>
<features>
<feature>Wood-burning fireplace in living room</feature>
<feature>Two large bedrooms with king beds and modern baths</feature>
<feature>Balcony</feature>
</features>
<activities>
<activity>Yellowbird ski resort within walking distance</activity>
<activity>Swimming available during summer in outdoor pool</activity>
</activities>
<restrictions>
<restriction>No pets</restriction>
</restrictions>
</propertyDescription>
The following propertyDescription document is the last document
in our collection. It describes an elegant single family home that
is available for rent:
<?xml version="1.0" encoding="UTF-8"?>
<!--
2006-004.xml
-->
<propertyDescription>
<propertyNumber>2006-004</propertyNumber>
<propertyName>141 Overlook</propertyName>
<propertyClass>Single Family Home</propertyClass>
<narrativeDescription>This five-bedroom luxury chalet is a great place
to unwind with family or friends during any season. In winter, ski all
day at nearby Yellowbird ski resort. In summer, swim in the private
pool, go hiking on Mount Wiggins, play golf, or just relax and enjoy
the mountain view. Take advantage of the gourmet
kitchen facilities to dine in, or take a night off and dine out at any of
the excellent area restaurants.</narrativeDescription>
<propertyAddress>
<street>141 Overlook Drive</street>
<city>Mountain Forest</city>
<state>CO</state>
<zip>12345</zip>
</propertyAddress>
<capacity>
<sleeps>10</sleeps>
</capacity>
<terms>
<dailyTerms>
<rent>1250</rent>
<deposit>800</deposit>
</dailyTerms>
<weekendTerms>
<rent>2000</rent>
<deposit>1500</deposit>
</weekendTerms>
<weeklyTerms>
<rent>10000</rent>
<deposit>7500</deposit>
</weeklyTerms>
</terms>
<owner>
<ownerName>Nancy K. Miller</ownerName>
<ownerAddress>
<street>c/o Miller, Fowlburn, Shreek & Hank</street>
<street>500 Legal Way</street>
<street>Suite 3420</street>
<city>Chicago</city>
<state>IL</state>
<zip>66666</zip>
</ownerAddress>
<ownerContact>
<contactName>Nancy K. Miller</contactName>
<contactPhone>312-555-5555</contactPhone>
</ownerContact>
</owner>
<features>
<feature>Five bedrooms, each with king bed, private bath and
wood-burning fireplace</feature>
<feature>Gourmet kitchen</feature>
<feature>Dining area with seating for 12</feature>
<feature>Lodge-like family room with large screen TV,
wood-burning fireplace, and floor-to-ceiling windows with
mountain view</feature>
<feature>Large outside deck with lounge chairs and umbrella
covered tables</feature>
<feature>Sauna</feature>
<feature>Whirlpool usable year round</feature>
<feature>Large private swimming pool usable in summer</feature>
</features>
<activities>
<activity>Ski at nearby Yellowbird Ski Resort</activity>
<activity>Swim in private pool or at public beach</activity>
<activity>Hike on Mt. Wiggins</activity>
<activity>Dine out at more than 30 fine area restaurants</activity>
<activity>Golf at any of three nearby championship courses</activity>
</activities>
<restrictions>
<restriction>No children</restriction>
<restriction>No pets</restriction>
<restriction>No drugs</restriction>
</restrictions>
</propertyDescription>
The following query operates on the whole collection of XML documents.
In this query, the FOR clause iterates over all three documents. So,
the RETURN clause generates output for each document:
(:
example_2.xquery
:)
for $prop in collection("rental_collection.xml")/propertyDescription
return $prop/propertyName
The results of the query are shown below. Readers who are familiar with
XML syntax will realize that this XML result document is not well-formed
since the propertyName elements are not enclosed in a single root or
docment element:
<?xml version="1.0" encoding="UTF-8"?>
<!--
example_2.xml
-->
<propertyName>Worthington Estate</propertyName>
<propertyName>2905 Yellowbird Apt. 4A</propertyName>
<propertyName>141 Overlook</propertyName>
Making the Results Well-Formed
Solving our well-formedness problem is fairly easy. The following query
wraps the code that we saw in the previous example in a properties
element. Note that the surrounding XML is separated from
the FOR and RETURN clauses by curly braces ({}). The code outside of the
curly braces is called an element constructor:
(:
example_3.xquery
:)
<properties>{
for $prop in collection("rental_collection.xml")/propertyDescription
return $prop/propertyName
}</properties>
The preceding query generates the following results. Note that
the resulting XML document is now well-formed because it has a root element
(properties):
<?xml version="1.0" encoding="UTF-8"?>
<!--
example_3.xml
-->
<properties>
<propertyName>Worthington Estate</propertyName>
<propertyName>2905 Yellowbird Apt. 4A</propertyName>
<propertyName>141 Overlook</propertyName>
</properties>
Wrapping the Results in XHTML
Gerating XML is helpful. But, it is often more helpful to generate XHTML.
The following query generates a fragment of an XHTML page that displays
the property names in an unordered list. As in the previous example,
element constructors have been used to generate additional markup. Note
that element constructors are still separated from the rest of the query
code by curly braces. Note also that the data() function has been used
to get the contents of elements from the source document into the result
document without their surrounding element tags:
(:
example_4.xquery
:)
<ul>{
for $prop in collection("rental_collection.xml")/propertyDescription
return <li>{data($prop/propertyName)}</li>
}</ul>
The preceding query generates the following results. Note that
none of the tags from the source documents are included in the
result document. XHTML tags have been generated with element
constructors and source tags have been suppressed using the
data() function:
<?xml version="1.0" encoding="UTF-8"?>
<!--
example_5.html
-->
<ul>
<li>Worthington Estate</li>
<li>2905 Yellowbird Apt. 4A</li>
<li>141 Overlook</li>
</ul>
Controlling the Results with Where and Order By
While the results thus far are interesting, they still could bear some
improvement. First, we need to be able to select properties on the
basis of some of the property's characteristics. Second, we need to be able to
sort the selected properties into alphabetical order.
The following query uses the WHERE clause to select only those
properties that offer daily rental (this does not include the Worthington
Estate). Also, it uses the ORDER BY clause to sort the results into
alphabetical order:
(:
example_6.xquery
:)
<ul>{
for $prop in collection("rental_collection.xml")/propertyDescription
where $prop/terms/dailyTerms
order by $prop/propertyName
return <li>{data($prop/propertyName)}</li>
}</ul>
Note that the following result document does not include the Worthington
Estate and that the properties are sorted in alphabetical order:
<?xml version="1.0" encoding="UTF-8"?>
<!--
example_6.html
-->
<ul>
<li>141 Overlook</li>
<li>2905 Yellowbird Apt. 4A</li>
</ul>
Generating a Useful XHTML Table
There are still a few improvements that can be made in our query.
First, more element constructors are needed in order to create an
XHTML fragment for a useful table. Second, more arguments are needed
in the RETURN clause to retrieve data needed to populate the
multiple columns of the table:
(:
example_7.xquery
:)
<table>
<tr>
<th>Property Name</th>
<th>Class</th>
<th>Sleeps</th>
<th>Rent</th>
<th>Deposit</th>
</tr>
{
for $prop in collection("rental_collection.xml")/propertyDescription
where $prop/terms/dailyTerms
order by $prop/propertyName
return
<tr>
<td>{data($prop/propertyName)}</td>
<td>{data($prop/propertyClass)}</td>
<td>{data($prop/capacity/sleeps)}</td>
<td>{data($prop/terms/dailyTerms/rent)}</td>
<td>{data($prop/terms/dailyTerms/deposit)}</td>
</tr>
}</table>
The results of our final query follow. Note that each row of the table
has been generated by the data in one of our XML input documents. Note
also that each row contains more than one value from the same XML input
document:
<?xml version="1.0" encoding="UTF-8"?>
<!--
example_7.html
-->
<table>
<tr>
<th>Property Name</th>
<th>Class</th>
<th>Sleeps</th>
<th>Rent</th>
<th>Deposit</th>
</tr>
<tr>
<td>141 Overlook</td>
<td>Single Family Home</td>
<td>10</td>
<td>1250</td>
<td>800</td>
</tr>
<tr>
<td>2905 Yellowbird Apt. 4A</td>
<td>Apartment</td>
<td>4</td>
<td>300</td>
<td>150</td>
</tr>
</table>
Future Articles
This article has just scratched the surface of what you can do with
XQuery. I hope that it has been enough to give you an overall XQuery
orientation and to interest you in learning more. I plan to write
several more Technical Tips articles about XQuery. Look for those
articles to address other XQuery features including:
- Using JOIN to combine data from multiple XML documents
- Querying documents using path expressions
- Grouping
- Aggregating values
- Querying XML and relational table data together
Make a Quick Start
Are you eager to get started with XQuery?
Inquire today about Caliber's
new course Querying XML Data With XQuery
and DB2 9. Caliber can be reached at 800-938-1222.