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:

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.