XQuery 1.1 Use Cases

1 Use Cases for XML Query 1.1

The use cases listed below were created by the XML Query Working Group to illustrate important applications for an XML query language. Each use case is focused on a specific application area, and contains a Document Type Definition (DTD) and example input data. Each use case specifies a set of queries that might be applied to the input data, and the expected results for each query. Since the English description of each query is concise, the expected results form an important part of the definition of each query, specifying the expected output format. These use cases were originally published as part of the [XQuery 1.1 Requirements] document, without solutions in concrete query languages. Now it is being republished with solutions for [XQuery].

The input environment for each use case is stated in its Document Type Definition (DTD) section. All of these use cases assume that input is provided in the form of one or more documents with specific names. For instance, the authors in a document may be accessed with expressions like this:

doc("bib.xml")//author

Some implementations of XQuery bind input to external variables. If the environment has bound the external variable $b to the same document used in the above query, this expression would return the same set of authors:

$b//author

Some implementations of XQuery predefine a single 'context item', which is available at the root level of a query, and which is used to resolve paths that begin with a leading slash. In such an implementation, if the context item is bound to document node of the same well-formed document used in the previous examples, this expression would return the same set of authors:

//author

Several implementors have asked us to make the queries from these use cases available in a separate file in order to make it easier for them to test their parsers. These queries may be found in [Use Case Sample Queries].

To make output more readable, the output of queries has been formatted using whitespace which may not be returned by a query processor. This whitespace should not be considered normative for the correctness of results.

These queries were tested with a dynamic implementation of XQuery. Some queries may require additional type declarations to be used with an implementation that implements the Static Typing feature.

2 Use Case "group by": Queries which require grouping

This use case contains several sample queries in which value based grouping is needed. Queries 1-6 are based on a simplified shop scenario with products, sale-records and different shop locations, whereas queries 7 and 8 are based on a bibliography document.

2.1 DTD for Q1-Q6

Most of the sample queries in this use case assume a simplified shop scenario described by three basic documents named products.xml, containing a list of products, stores.xml, containing a list of stores and sales-records.xml, including a list of sales. The product.xml uses the following Document Type Definitions (DTD):

<!ELEMENT products (product*)>
<!ELEMENT product (name, category, price, cost)>
<!ELEMENT name (#PCDATA)>
<!ELEMENT category (#PCDATA)>
<!ELEMENT price (#PCDATA)>
<!ELEMENT cost (#PCDATA)>

The sales-records.xml document follows this DTD:

<!ELEMENT sales (record*)>
<!ELEMENT record (product-name, store-number, qty)>
<!ELEMENT product-name (#PCDATA)>
<!ELEMENT store-number (#PCDATA)>
<!ELEMENT qty (#PCDATA)>

The stores.xml document is valid to this DTD:

<!ELEMENT stores (store*)>
<!ELEMENT store (store-number, state)>
<!ELEMENT store-number (#PCDATA)>
<!ELEMENT state (#PCDATA)>

2.2 Sample Data

The content of products.xml is:

<products>
  <product>
    <name>broiler</name>
    <category>kitchen</category>
    <price>100</price>
    <cost>70</cost>
  </product>
  <product>
    <name>toaster</name>
    <category>kitchen</category>
    <price>30</price>
    <cost>10</cost>
  </product>
  <product>
    <name>blender</name>
    <category>kitchen</category>
    <price>50</price>
    <cost>25</cost>
  </product>
  <product>
    <name>socks</name>
    <category>clothes</category>
    <price>5</price>
    <cost>2</cost>
  </product>
  <product>
    <name>shirt</name>
    <category>clothes</category>
    <price>10</price>
    <cost>3</cost>
  </product>
</products>

The content of sales-records.xml is:

<sales>
  <record>
    <product-name>broiler</product-name>
    <store-number>1</store-number>
    <qty>20</qty>
  </record>
  <record>
    <product-name>toaster</product-name>
    <store-number>2</store-number>
    <qty>100</qty>
  </record>
  <record>
    <product-name>toaster</product-name>
    <store-number>2</store-number>
    <qty>50</qty>
  </record>
  <record>
    <product-name>toaster</product-name>
    <store-number>3</store-number>
    <qty>50</qty>
  </record>
  <record>
    <product-name>blender</product-name>
    <store-number>3</store-number>
    <qty>100</qty>
  </record>
  <record>
    <product-name>blender</product-name>
    <store-number>3</store-number>
    <qty>150</qty>
  </record>
  <record>
    <product-name>socks</product-name>
    <store-number>1</store-number>
    <qty>500</qty>
  </record>
  <record>
    <product-name>socks</product-name>
    <store-number>2</store-number>
    <qty>10</qty>
  </record>
  <record>
    <product-name>shirt</product-name>
    <store-number>3</store-number>
    <qty>10</qty>
  </record>
</sales>

The content of stores.xml is:

<stores>
  <store>
    <store-number>1</store-number>
    <state>CA</state>
  </store>
  <store>
    <store-number>2</store-number>
    <state>CA</state>
  </store>
  <store>
    <store-number>3</store-number>
    <state>MA</state>
  </store>
</stores>

2.3 DTD for Q7-Q8

Q7 and Q8 use an input document named "books.xml", with the following DTD:

<!ELEMENT bib ((book+))>
<!ELEMENT book ((author+, title))>
<!ELEMENT author (#PCDATA)>
<!ELEMENT title (#PCDATA)>

2.4 Sample Data

Here are the contents of books.xml:

<bib>
  <book>
    <author>Jim Melton</author>
    <author>Alan Simon</author>
    <title>SQL:1999</title>
  </book>
  <book>
    <author>Jim Melton</author>
    <title>Advanced SQL:1999</title>
  </book>
  <book>
    <author>Alan Simon</author>
    <title>Strategic Database Technology</title>
  </book>
  <book>
    <author>Jim Melton</author>
    <author>Andrew Eisenberg</author>
    <title>Understanding SQL and Java Together</title>
  </book>
  <book>
    <author>Jim Melton</author>
    <author>Stephen Buxton</author>
    <title>Querying XML</title>
  </book>
</bib>

2.5 Queries and Results

2.5.1 Q1

Group sales by product, list name and total quantity of each product.

Solution in XQuery:

<sales-qty-by-product>{
  for $sales in doc("sales-records.xml")/*/record
  let $pname := $sales/product-name
  group by $pname
  order by $pname
  return
    <product name="{$pname}">{
      sum($sales/qty)
    }</product>
}</sales-qty-by-product>

Expected Result:

<sales-qty-by-product>
  <product name="blender">250</product>
  <product name="broiler">20</product>
  <product name="shirt">10</product>
  <product name="socks">510</product>
  <product name="toaster">200</product>
</sales-qty-by-product>

2.5.2 Q2

Group sales by state (a property of the store) and category (a property of the product). Order groups by the grouping keys. For each group, show the total quantity sold.

Solution in XQuery:

<result>{   
  for $sales in doc("sales-records.xml")/*/record
  let $state := doc("stores.xml")/*/store[store-number = $sales/store-number]/state
  let $category := doc("products.xml")/*/product[name = $sales/product-name]/category
  group by $state, $category
  order by $state, $category
  return
    <group>
      {$state, $category}
      <total-qty>{sum($sales/qty)}</total-qty>
    </group>
}</result>

Expected Result:

<result>
  <group>
    <state>CA</state>
    <category>clothes</category>
    <total-qty>510</total-qty>
  </group>
  <group>
    <state>CA</state>
    <category>kitchen</category>
    <total-qty>170</total-qty>
  </group>
  <group>
    <state>MA</state>
    <category>clothes</category>
    <total-qty>10</total-qty>
  </group>
  <group>
    <state>MA</state>
    <category>kitchen</category>
    <total-qty>300</total-qty>
  </group>
</result>

2.5.3 Q3

Group sales by state (a property of the store) and category (a property of the product). Order groups by the grouping keys. For each group, show the total revenue (defined as sales/qty * product/price).

Solution in XQuery:

<result>{
  for $sales in doc("sales-records.xml")/*/record
  let $state := doc("stores.xml")/*/store[store-number = $sales/store-number]/state,
    $product := doc("products.xml")/*/product[name = $sales/product-name],
    $category := $product/category,
    $revenue := $sales/qty * $product/price
  group by $state, $category
  order by $state, $category
  return
    <group>
      {$state, $category}
      <total-revenue>{sum($revenue)}</total-revenue>
    </group> 
}</result>

Expected Result:

<result>
  <group>
    <state>CA</state>
    <category>clothes</category>
    <total-revenue>2550</total-revenue>
  </group>
  <group>
    <state>CA</state>
    <category>kitchen</category>
    <total-revenue>6500</total-revenue>
  </group>
  <group>
    <state>MA</state>
    <category>clothes</category>
    <total-revenue>100</total-revenue>
  </group>
  <group>
    <state>MA</state>
    <category>kitchen</category>
    <total-revenue>14000</total-revenue>
  </group>
</result>

2.5.4 Q4

Combine the input documents into a three-level hierarchy based on state, category, and product. At the product level, show the total quantity sold of each product. List items alphabetically at each level of the hierarchy.

Solution in XQuery:

<result>{
  for $store in doc("stores.xml")/*/store
  let $state := $store/state
  group by $state
  order by $state
  return
    <state name="{$state}">{
      for $product in doc("products.xml")/*/product
      let $category := $product/category
      group by $category
      order by $category
      return
        <category name="{$category}">{
          for $sales in doc("sales-records.xml")/*/record[store-number = $store/store-number
            and product-name = $product/name]
          let $pname := $sales/product-name
          group by $pname
          order by $pname
          return
            <product name="{$pname}" total-qty="{sum($sales/qty)}" />
          }</category>
    }</state> 
}</result>

Expected Result:

<result>
  <state name="CA">
    <category name="clothes">
      <product name="socks" total-qty="510"/>
    </category>
    <category name="kitchen">
      <product name="broiler" total-qty="20"/>
      <product name="toaster" total-qty="150"/>
    </category>
  </state>
  <state name="MA">
    <category name="clothes">
      <product name="shirt" total-qty="10"/>
    </category>
    <category name="kitchen">
      <product name="blender" total-qty="250"/>
      <product name="toaster" total-qty="50"/>
    </category>
  </state>
</result>

2.5.5 Q5

List all stores in ascending order by store number. For each store, list the products sold in that store, in descending order by quantity sold. Illustrates ordering among and within groups.

Solution in XQuery:

<result>{
  for $sales in doc("sales-records.xml")/*/record
  let $storeno := $sales/store-number
  group by $storeno
  order by $storeno
  return
    <store number = "{$storeno}">{
      for $s in $sales
      order by xs:int($s/qty) descending
      return
        <product name = "{$s/product-name}" qty = "{$s/qty}"/>
    }</store> 
}</result>

Expected Result:

<result>
  <store number="1">
    <product name="socks" qty="500"/>
    <product name="broiler" qty="20"/>
  </store>
  <store number="2">
    <product name="toaster" qty="100"/>
    <product name="toaster" qty="50"/>
    <product name="socks" qty="10"/>
  </store>
  <store number="3">
    <product name="blender" qty="150"/>
    <product name="blender" qty="100"/>
    <product name="toaster" qty="50"/>
    <product name="shirt" qty="10"/>
  </store>
</result>

2.5.6 Q6

List all stores whose total profit is greater than 100, in descending order by total profit. Note: total profit for a store is the sum over all sales in that store, of the quantity sold times the difference between price and cost for the item sold. Illustrates cross-document computation, filtering of groups, ordering by a non-grouping-key.

Solution in XQuery:

<result>{
  for $sales in doc("sales-records.xml")/*/record
  let $storeno := $sales/store-number,
    $product := doc("products.xml")/*/product[name = $sales/product-name],
    $prd := $product,
    $profit := $sales/qty * ($prd/price - $prd/cost)
  group by $storeno
  let $total-store-profit := sum($profit)
  where $total-store-profit > 100
  order by $total-store-profit descending
  return
    <store number = "{$storeno}" total-profit = "{$total-store-profit}"/> 
 }</result>

Expected Result:

<result>
  <store number="3" total-profit="7320"/>
  <store number="2" total-profit="3030"/>
  <store number="1" total-profit="2100"/>
</result>

2.5.7 Q7

Group books by author. Create a group for each individual author. A book with multiple authors should appear in the groups for each of its authors. Alphebetize the authors and the book titles within each author-group.

Solution in XQuery:

<result>{
  for $book in doc("books.xml")/*/book
  for $author in $book/author
  group by $author
  order by $author
  return
  <author name="{$author}">{ 
    for $b in $book
    order by $b/title
    return
      <title> {fn:data($b/title)} </title>
  }</author> 
}</result>

Expected Result:

<result>
  <author name="Alan Simon">
    <title>SQL:1999</title>
    <title>Strategic Database Technology</title>
  </author>
  <author name="Andrew Eisenberg">
    <title>Understanding SQL and Java Together</title>
  </author>
  <author name="Jim Melton">
    <title>Advanced SQL:1999</title>
    <title>Querying XML</title>
    <title>SQL:1999</title>
    <title>Understanding SQL and Java Together</title>
  </author>
  <author name="Stephen Buxton">
    <title>Querying XML</title>
  </author>
</result>

2.5.8 Q8

Group books by author. Create a group for each distinct ordered list of authors. Each book should be grouped with other books that have the same ordered list of authors. Alphebetize the book titles within each group.

Solution in XQuery:

<result>{
  for $book in doc("books.xml")/*/book
  let $author-list := fn:string-join($book/author, ', ')
  group by $author-list
  order by $author-list
  return
    <author-list names="{$author-list}">{ 
      for $b in $book
      order by $b/title
      return
        <title> {fn:data($b/title)} </title>
    }</author-list> 
}</result>

Expected Result:

<result>
  <author-list names="Alan Simon">
    <title>Strategic Database Technology</title>
  </author-list>
  <author-list names="Jim Melton">
    <title>Advanced SQL:1999</title>
  </author-list>
  <author-list names="Jim Melton, Alan Simon">
    <title>SQL:1999</title>
  </author-list>
  <author-list names="Jim Melton, Andrew Eisenberg">
    <title>Understanding SQL and Java Together</title>
  </author-list>
  <author-list names="Jim Melton, Stephen Buxton">
    <title>Querying XML</title>
  </author-list>
</result>

3 Use Case "Windowing": Queries which require windowing

This use case covers queries that require windowing or positional grouping, which can be seen as a special form of windowing. Windowing means that the queries require selecting subsequences based on certain characterisics of an underlying sequence.

3.1 DTD for Q1

Q1 uses an input document named arrange_rows.xml, with the following DTD:

<!ELEMENT doc ((data+))>
<!ELEMENT data (#PCDATA)>

3.2 Sample Data

The content of arrange_rows.xml is:

<doc>
  <data>Green</data>
  <data>Pink</data>
  <data>Lilac</data>
  <data>Turquoise</data>
  <data>Peach</data>
  <data>Opal</data>
  <data>Champagne</data>
</doc>

3.3 DTD for Q2

Q2 uses an input document named head_para.xml, with the following DTD:

<!ELEMENT body ((h2, p*)*)>
<!ELEMENT p (#PCDATA)>
<!ELEMENT h2 (#PCDATA)>

3.4 Sample Data

The content of head_para.xml is:

<body>
  <h2>heading1</h2>
  <p>para1</p>
  <p>para2</p>
  <h2>heading2</h2>
  <p>para3</p>
  <p>para4</p>
  <p>para5</p>
</body>

3.5 DTD for Q3

Q3 uses an input document named term_def_list.xml, with the following DTD:

<!ELEMENT doc ((dt+, dd+)*)>
<!ELEMENT dt (#PCDATA)>
<!ELEMENT dd (#PCDATA)>

3.6 Sample Data

The content of term_def_list.xml is:

<doc>
  <dt>XML</dt>
  <dd>Extensible Markup Language</dd>
  <dt>XSLT</dt>
  <dt>XSL Transformations</dt>
  <dd>A language for transforming XML</dd>
  <dd>A specification produced by W3C</dd>
</doc>

3.7 DTD for Q4-Q6

Q4 - Q6 use an input document named temp_events.xml, with the following DTD:

<!ELEMENT stream ((event+))>
<!ELEMENT event EMPTY>
<!ATTLIST event
        temp CDATA #REQUIRED
        time CDATA #REQUIRED
>

3.8 Sample Data

The content of temp_events.xml is:

<stream>
  <event temp="10" time="1"/>
  <event temp="8" time="2"/>
  <event temp="6" time="3"/>
  <event temp="13" time="4"/>
  <event temp="33" time="5"/>
  <event temp="10" time="6"/>
  <event temp="10" time="7"/>
</stream>

3.9 DTD for Q7-Q13

Q7 - Q13 use an input document named person_events.xml, with the following DTD:

<!ELEMENT stream (event+)>
<!ELEMENT event ((person, direction)?)>
<!ATTLIST event time CDATA #REQUIRED>
<!ELEMENT person (#PCDATA)>
<!ELEMENT direction (#PCDATA)>

3.10 Sample Data

The content of person_events.xml is:

<stream>
  <event time="2006-01-01T01:00:00-00:00"/>
  <event time="2006-01-01T10:30:00-00:00">
    <person>Anton</person>
    <direction>in</direction>
  </event>
  <event time="2006-01-01T11:00:00-00:00">
    <person>Barbara</person>
    <direction>in</direction>
  </event>
  <event time="2006-01-01T11:15:00-00:00">
    <person>Clara</person>
    <direction>in</direction>
  </event>
  <event time="2006-01-01T12:15:00-00:00">
    <person>Clara</person>
    <direction>out</direction>
  </event>
  <event time="2006-01-01T14:00:00-00:00">
    <person>Barbara</person>
    <direction>out</direction>
  </event>
  <event time="2006-01-01T15:00:00-00:00">
    <person>Anton</person>
    <direction>out</direction>
  </event>
  <event time="2006-01-01T23:00:00-00:00"/>
  <event time="2006-01-02T01:00:00-00:00"/>
  <event time="2006-01-02T11:00:00-00:00">
    <person>Anton</person>
    <direction>in</direction>
  </event>
  <event time="2006-01-02T12:00:00-00:00">
    <person>Clara</person>
    <direction>in</direction>
  </event>
  <event time="2006-01-02T16:00:00-00:00">
    <person>Anton</person>
    <direction>out</direction>
  </event>
  <event time="2006-01-02T16:15:00-00:00">
    <person>Clara</person>
    <direction>out</direction>
  </event>
  <event time="2006-01-02T23:00:00-00:00"/>
</stream>

3.11 DTD for Q14-Q16

Q14 - Q16 use as input document a slightly modified RSS document named rss.xml, with the following DTD :

<!ELEMENT rss (channel*)>
<!ATTLIST rss version CDATA #REQUIRED>
<!ELEMENT channel ((title, link, description, language, item*))>
<!ELEMENT description (#PCDATA)>
<!ELEMENT language (#PCDATA)>
<!ELEMENT link (#PCDATA)>
<!ELEMENT item ((title, category, author, pubDate))>
<!ELEMENT pubDate (#PCDATA)>
<!ELEMENT category (#PCDATA)>
<!ELEMENT author (#PCDATA)>
<!ELEMENT title (#PCDATA)>

3.12 Sample Data

The main difference to RSS 2.0 how dates are expressed in the data. The content of rss.xml is:

<rss version="2.0">
  <channel>
    <title>DBIS RSS</title>
    <link>http://www.dbis.ethz.ch</link>
    <description>The windowing dummy RSS.</description>
    <language>en-us</language>
    <item>
      <title>Why use cases are important Part 1.</title>
      <category>Workshop</category>
      <author>rokas@e-mail.de</author>
      <pubDate>2003-06-03T09:00:00</pubDate>
    </item>
    <item>
      <title>Why use cases are important Part 2.</title>
      <category>Workshop</category>
      <author>rokas@e-mail.de</author>
      <pubDate>2003-06-03T09:00:00</pubDate>
    </item>
    <item>
      <title>Why use cases are important Part 3.</title>
      <category>Workshop</category>
      <author>rokas@e-mail.de</author>
      <pubDate>2003-06-03T10:00:00</pubDate>
    </item>
    <item>
      <title>Extending XQuery with Window Functions</title>
      <category>Talk</category>
      <author>tim@e-mail.de</author>
      <pubDate>2003-06-03T11:00:00</pubDate>
    </item>
    <item>
      <title>XQueryP: A new programming language is born</title>
      <category>Talk</category>
      <author>david@e-mail.de</author>
      <pubDate>2003-06-03T12:00:00</pubDate>
    </item>
    <item>
      <title>Why use cases are annoying to write.</title>
      <category>Talk</category>
      <author>rokas@e-mail.de</author>
      <pubDate>2003-06-04T10:00:00</pubDate>
    </item>
  </channel>
</rss>

3.13 DTD for Q17-Q19

Q17-Q19 use an input document named cxml.xml. The structure of this document is inspired by the Commerce XML Resource standard (cXML). The document contains a sequence of events, whereas an event corresponds to a simplified message of the cXML standard or an timeclock event. The DTD is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<!ELEMENT sequence ((time, OrderRequest, ConfirmationRequest,ShipNotice)*)>
<!ELEMENT time EMPTY>
<!ATTLIST time timedate CDATA #REQUIRED>
<!ELEMENT ShipNotice EMPTY>
<!ATTLIST ShipNotice date CDATA #REQUIRED    orderID CDATA #REQUIRED>
<!ELEMENT OrderRequest ((Item+))>
<!ATTLIST OrderRequest
        billTo CDATA #REQUIRED
        date CDATA #REQUIRED
        orderID CDATA #REQUIRED
        shipTo CDATA #IMPLIED
        total CDATA #REQUIRED
        type CDATA #REQUIRED
>
<!ELEMENT Item EMPTY>
<!ATTLIST Item
        partID CDATA #REQUIRED
        quantity CDATA #REQUIRED
        unitPrice CDATA #REQUIRED
>
<!ELEMENT ConfirmationRequest EMPTY>
<!ATTLIST ConfirmationRequest
        confirmID CDATA #REQUIRED
        date CDATA #REQUIRED
        orderID CDATA #REQUIRED
        status CDATA #REQUIRED
>

3.14 Sample Data

The content of cxml.xml is:

<sequence>
  <time date="2006-01-01T00:00:00-00:00"/>
  <OrderRequest billTo="ACME1" date="2006-01-01T10:00:00-00:00"
    orderID="OID01" shipTo="ACME1" total="1100" type="new">
    <Item partID="ID1" quantity="10" unitPrice="100"/>
    <Item partID="ID2" quantity="10" unitPrice="10"/>
  </OrderRequest>
  <OrderRequest billTo="ACME2" date="2006-01-01T11:00:00-00:00"
    orderID="OID02" total="100" type="new">
    <Item partID="ID2" quantity="10" unitPrice="10"/>
  </OrderRequest>
  <ConfirmationRequest confirmID="C1" date="2006-01-01T18:00:00-00:00"
    orderID="OID02" status="reject"/>
  <time date="2006-01-02T00:00:00-00:00"/>
  <ConfirmationRequest confirmID="C1" date="2006-01-02T08:00:00-00:00"
    orderID="OID01" status="accept"/>
  <OrderRequest billTo="ACME1" date="2006-01-02T14:00:00-00:00"
    orderID="OID03" shipTo="ACME1" total="10000" type="new">
    <Item partID="ID3" quantity="100" unitPrice="100"/>
  </OrderRequest>
  <ConfirmationRequest confirmID="C1" date="2006-01-02T16:00:00-00:00"
    orderID="OID03" status="accept"/>
  <time date="2006-01-03T00:00:00-00:00"/>
  <time date="2006-01-04T00:00:00-00:00"/>
  <time date="2006-01-05T00:00:00-00:00"/>
  <ShipNotice date="2006-01-05T08:00:00-00:00" orderID="OID01"/>
  <ShipNotice date="2006-01-05T09:00:00-00:00" orderID="OID03"/>
  <time date="2006-01-06T00:00:00-00:00"/>
  <OrderRequest billTo="ACME2" date="2006-01-06T08:00:00-00:00"
    orderID="OID04" total="100" type="new">
    <Item partID="ID2" quantity="10" unitPrice="10"/>
  </OrderRequest>
  <time date="2006-01-07T00:00:00-00:00"/>
</sequence>

3.15 Queries and Results

3.15.1 Q1

Arrange a sequence of items as a table with three columns (using as many rows as necessary).

Solution in XQuery:

declare variable $seq := fn:doc("arrange_rows.xml");

<table>{
  for tumbling window $w in $seq/doc/*
    start at $x when fn:true() 
    end at $y when $y - $x = 2
  return
    <tr>{
      for $i in $w
      return
        <td>{data($i)}</td>
    }</tr>
}</table>

Expected Result:

<table>
  <tr>
    <td>Green</td>
    <td>Pink</td>
    <td>Lilac</td>
  </tr>
  <tr>
    <td>Turquoise</td>
    <td>Peach</td>
    <td>Opal</td>
  </tr>
  <tr>
    <td>Champagne</td>
  </tr>
</table>

3.15.2 Q2

Convert a structure with implicit sections to a structure with explicit sections.

Solution in XQuery:

declare variable $seq := fn:doc("head_para.xml");

<chapter>{
  for tumbling window $w in $seq/body/* 
    start previous $s when $s[self::h2]  
    end at $e when $e[self::h2] 
  return
    <section title="{data($s)}">{
       for $x in $w 
       return
         <para>{data($x)}</para>
  }</section>
}</chapter>

Expected Result:

<chapter>
  <section title="heading1">
    <para>para1</para>
    <para>para2</para>
    <para>heading2</para>
  </section>
  <section title="heading2">
    <para>para3</para>
    <para>para4</para>
    <para>para5</para>
  </section>
</chapter>

3.15.3 Q3

Within a glossary in HTML, a defined term <dt> can be followed by a definition <dd>. The task is to group these together within a <term> element, where a group can consist of one or more <dt> elements followed by one or more <dd> elements.

Solution in XQuery:

declare variable $seq := fn:doc("term_def_list.xml");

<doc>{
for tumbling window $w in $seq/doc/*
  start at $x when $x[self::dt]
  end at $y next $z when $y[self::dd] and $z[self::dt]
return
  <term>{
    $w
  }</term>
}</doc>

Expected Result:

<doc>
  <term>
    <dt>XML</dt>
    <dd>Extensible Markup Language</dd>
  </term>
  <term>
    <dt>XSLT</dt>
    <dt>XSL Transformations</dt>
    <dd>A language for transforming XML</dd>
    <dd>A specification produced by W3C</dd>
  </term>
</doc>

3.15.4 Q4

Calculate the moving average of temperature values for the 3 last seconds.

Solution in XQuery:

declare variable $timesequence := fn:doc("temp_events.xml");

let $MAX_DIFF := 2

for sliding window $w in $timesequence/stream/event 
  start  $s_curr at $s_pos previous $s_prev
    when ($s_curr/@time ne $s_prev/@time) or (empty($s_prev))
  only end next $e_next 
    when $e_next/@time - $s_curr/@time gt $MAX_DIFF
return 
  avg( $w/@temp )

Expected Result:

8 9 17 18

3.15.5 Q5

Single Exponential Smoothing (3 last values and smoothing-factor 0.2)

Solution in XQuery:

declare variable $timesequence := fn:doc("temp_events.xml");
let $SMOOTH_CONST := 0.2

for sliding window $w in $timesequence/stream/event
  start at $s_pos when true()
  only end at $e_pos when $e_pos - $s_pos eq 2
return 
  round-half-to-even($SMOOTH_CONST * data($w[3]/@temp) + (1 - $SMOOTH_CONST) *
    ( $SMOOTH_CONST * data($w[2]/@temp) + 
      (1 - $SMOOTH_CONST) * data($w[1]/@temp) ), 2)

Expected Result:

8.88 8.68 12.32 15.24 23.92

3.15.6 Q6

Detect outliers (current value is two times higher (lower) than the previous one) in a sequence of temp values.

Solution in XQuery:

declare variable $seq := fn:doc("temp_events.xml");

for sliding window $w in $seq/stream/event 
  start  $s_curr when fn:true()
  only end next $next when $next/@time > $s_curr/@time + 3
return
  let $avg := fn:avg($w/@temp)
  where $avg * 2 lt xs:double($next/@temp) or $avg div 2 gt xs:double($next/@temp)
  return <alarm>Outlier detected. Event id:{data($next/@time)}</alarm>

Expected Result:

<alarm>Outlier detected. Event id:5</alarm>

3.15.7 Q7

Notify when Barbara enters the building within 1 hour after Anton

Solution in XQuery:

declare variable $seq := fn:doc("person_events.xml");

<result>{
  for tumbling window $w in $seq/stream/event 
    start  $s when $s/person eq "Anton" and $s/direction eq "in"
    only end $e next $n when  xs:dateTime($n/@time) - xs:dateTime($s/@time) gt 
      xs:dayTimeDuration("PT1H") or  $e/person eq "Barbara" and $e/direction eq "in"
  where $e/person eq "Barbara" and $e/direction eq "in"
  return 
    <warning time="{ $e/@time }">Barbara: Anton arrived 1h ago</warning>
}</result>

Expected Result:

<result>
  <warning time="2006-01-01T11:00:00-00:00">Barbara: Anton arrived 1h ago</warning>
</result>

3.15.8 Q8

Measure the working time of each person

Solution in XQuery:

declare variable $seq := fn:doc("person_events.xml");

<result>{
for sliding window $w in $seq/stream/event
  start  $s when $s/direction eq "in"
  only end  $e when $s/person eq $e/person and
    $e/direction eq "out"
return
  <working-time> 
      {$s/person}
      <time>{ xs:dateTime($e/@time) - xs:dateTime($s/@time)}</time>
  </working-time>
}</result>

Expected Result:

<result>
  <working-time>
    <person>Clara</person>
    <time>PT1H</time>
  </working-time>
  <working-time>
    <person>Barbara</person>
    <time>PT3H</time>
  </working-time>
  <working-time>
    <person>Anton</person>
    <time>PT4H30M</time>
  </working-time>
  <working-time>
    <person>Clara</person>
    <time>PT10M</time>
  </working-time>
  <working-time>
    <person>Clara</person>
    <time>PT5M</time>
  </working-time>
  <working-time>
    <person>Clara</person>
    <time>PT15M</time>
  </working-time>
  <working-time>
    <person>Anton</person>
    <time>PT5H</time>
  </working-time>
  <working-time>
    <person>Clara</person>
    <time>PT2H15M</time>
  </working-time>
</result>

3.15.9 Q9

Measure the overall working time for each person.

Solution in XQuery:

declare variable $seq := fn:doc("person_events.xml");

<result>{
for sliding window $w in $seq/stream/event 
  start  $s when $s/direction eq "in"
  only end  $e when $s/person eq $e/person and
    $e/direction eq "out"
let $person := $s/person
let $workingTime := xs:dateTime($e/@time) - xs:dateTime($s/@time)
group by $person
return
  <working-time>
    {$person}
    <time>{ sum($workingTime) }</time>
  </working-time>
    
}</result>

Expected Result:

<result>
  <working-time>
    <person>Barbara</person>
    <time>PT3H</time>
  </working-time>
  <working-time>
    <person>Anton</person>
    <time>PT9H30M</time>
  </working-time>
  <working-time>
    <person>Clara</person>
    <time>PT3H45M</time>
  </working-time>
</result>

3.15.10 Q10

Display a warning if Barbara does not come to work.

Solution in XQuery:

declare variable $seq := fn:doc("person_events.xml");

<result>{
  for tumbling window $w in $seq/stream/event[direction eq "in"]
    start  $s when fn:true() 
    end next $e when xs:date( xs:dateTime($s/@time) ) ne xs:date( xs:dateTime($e/@time) ) 
  let $date := xs:date(xs:dateTime($s/@time))
  where not($w[person eq "Barbara"])
  return <alert date="{ $date }">Barbara did not come to work</alert>
}</result>

Expected Result:

<result>
  <alert date="2006-01-02Z">Barbara did not come to work</alert>
</result>

3.15.11 Q11

Identify every person who arrives at least 15 minutes earlier than Clara.

Solution in XQuery:

declare variable $seq := fn:doc("person_events.xml");

<result>{
  for tumbling window $w in $seq/stream/event
    start when true()
    only end  $x when  $x/person eq "Clara" and $x/direction eq "in"
  return 
    <result time="{ $x/@time }">{
      distinct-values(for $y in $w 
        where (xs:dateTime($y/@time) + xs:dayTimeDuration("PT15M") ) lt xs:dateTime($x/@time)
        return $y/person)
    }</result>
}</result>

Expected Result:

<result>
  <result time="2006-01-01T11:15:00-00:00">Anton</result>
  <result time="2006-01-02T11:00:00-00:00">Clara Barbara Anton</result>
  <result time="2006-01-02T11:15:00-00:00"/>
  <result time="2006-01-02T11:30:00-00:00"/>
  <result time="2006-01-02T14:00:00-00:00">Clara</result>
</result>

3.15.12 Q12

Notify when both Anton and Barbara enter the office within 30 minutes of one another.

Solution in XQuery:

declare variable $seq := fn:doc("person_events.xml");

<result>{
        for tumbling window $w in $seq/stream/event[direction eq "in"] 
                start  $x when $x/person = ("Barbara", "Anton")
                end next $y when xs:dateTime($y/@time) - xs:dateTime($x/@time) gt xs:dayTimeDuration("PT30M")
        where $w[person eq "Anton"] and $w[person eq "Barbara"]
        return 
                <alert date="{ xs:dateTime($y/@time) }">Anton and Barbara just arrived</alert>
}</result>

Expected Result:

<result>
  <alert date="2006-01-01T11:15:00Z">Anton and Barbara just arrived</alert>
</result>

3.15.13 Q13

Inform when a person entered the building at least 3 times within 1 hour

Solution in XQuery:

declare variable $seq := fn:doc("person_events.xml");

<result>{
  for sliding window $w in $seq/stream/event 
    start  $s when true() 
    end next $e when xs:dateTime($e/@time) - xs:dateTime($s/@time) gt 
      xs:dayTimeDuration("PT1H")
  where count($w[person eq $s/person and direction eq "in"]) ge 3
  return 
    <alert time="{ $e/@time }">{fn:data($s/person)} is suspicious</alert>
}</result>

Expected Result:

<result>
  <alert time="2006-01-02T14:00:00-00:00">Clara is suspicious</alert>
</result>

3.15.14 Q14

Find all annoying authors who have posted three consecutive items in the RSS feed.

Solution in XQuery:

declare variable $rssfeed := fn:doc("rss.xml");

<result>{
  for tumbling window $w in $rssfeed/rss/channel/item 
    start  $first when fn:true()
    end next $lookAhead when $first/author ne $lookAhead/author
  where count($w) ge 3
  return <annoying-author>{
      $w[1]/author
    }</annoying-author>
}</result>

Expected Result:

<result>
  <annoying-author>
    <author>rokas@e-mail.de</author>
  </annoying-author>
</result>

3.15.15 Q15

Every day provide a list of interesting topics in the RSS feed. In our example interesting means, that the title of the item contains the specific word XQuery.

Solution in XQuery:

declare variable $rssfeed := fn:doc("rss.xml");

<result>{
  for tumbling window $w in $rssfeed/rss/channel/item 
    start  $s_curr when true()
    end next $e_next when 
      fn:day-from-dateTime(xs:dateTime($e_next/pubDate)) ne 
      fn:day-from-dateTime(xs:dateTime($s_curr/pubDate))
  return 
    <item>
        <date>{xs:date(xs:dateTime($s_curr/pubDate))}</date>
        {  for $item in $w 
                   where fn:contains( xs:string($item/title), 'XQuery')
                   return $item/title   }
      </item>
}</result>

Expected Result:

<result>
  <item>
    <date>2003-06-03</date>
    <title>Extending XQuery with Window Functions</title>
    <title>XQueryP: A new programming language is born</title>
  </item>
  <item>
    <date>2003-06-04</date>
  </item>
</result>

3.15.16 Q16

Every day, provide a summary of the RSS feed grouped by author.

Solution in XQuery:

declare variable $rssfeed := fn:doc("rss.xml");

<result>{
  for tumbling window $w in $rssfeed/rss/channel/item 
    start  $s_curr when true()
    end next $e_next when 
      fn:day-from-dateTime(xs:dateTime($e_next/pubDate)) ne 
      fn:day-from-dateTime(xs:dateTime($s_curr/pubDate))
  return 
    <item>
      <date>{xs:date(xs:dateTime($s_curr/pubDate))}</date>
       {  for $a in fn:distinct-values($w/author) 
           return 
             <author name="{$a}">
               <titles>
                 { $w[author eq $a]/title }
               </titles>
                        </author>
            }
          </item>
}</result>

Expected Result:

<result>
  <item>
    <date>2003-06-03</date>
    <author name="rokas@e-mail.de">
      <titles>
        <title>Why use cases are important Part 1.</title>
        <title>Why use cases are important Part 2.</title>
        <title>Why use cases are important Part 3.</title>
      </titles>
    </author>
    <author name="tim@e-mail.de">
      <titles>
        <title>Extending XQuery with Window Functions</title>
      </titles>
    </author>
    <author name="david@e-mail.de">
      <titles>
        <title>XQueryP: A new programming language is born</title>
      </titles>
    </author>
  </item>
  <item>
    <date>2003-06-04</date>
    <author name="rokas@e-mail.de">
      <titles>
        <title>Why use cases are annoying to write.</title>
      </titles>
    </author>
  </item>
</result>

3.15.17 Q17

At the end of a day, list the most valuable customers.

Solution in XQuery:

declare variable $seq := fn:doc("cxml.xml");

<result>{
for sliding window $w in $seq/sequence/* 
  start  $cur previous $prev 
   when day-from-date(xs:dateTime($cur/@date)) ne day-from-date(xs:dateTime($prev/@date)) or empty($prev)
  end when newstart
return
  <mostValuableCustomer endOfDay="{xs:dateTime($cur/@date)}">{
    let $companies :=   for $x in distinct-values($w/@billTo ) 
                        return <amount company="{$x}">{sum($w[@billTo eq $x]/@total)}</amount>
    let $max := max($companies) 
    for $company in $companies
    where $company eq xs:untypedAtomic($max)
    return $company
  }</mostValuableCustomer>
}</result>

Expected Result:

<result>
  <mostValuableCustomer endOfDay="2006-01-01T00:00:00Z">
    <amount company="ACME1">1100</amount>
  </mostValuableCustomer>
  <mostValuableCustomer endOfDay="2006-01-02T00:00:00Z">
    <amount company="ACME1">10000</amount>
  </mostValuableCustomer>
  <mostValuableCustomer endOfDay="2006-01-03T00:00:00Z"/>
  <mostValuableCustomer endOfDay="2006-01-04T00:00:00Z"/>
  <mostValuableCustomer endOfDay="2006-01-05T00:00:00Z"/>
  <mostValuableCustomer endOfDay="2006-01-06T00:00:00Z">
    <amount company="ACME2">100</amount>
  </mostValuableCustomer>
  <mostValuableCustomer endOfDay="2006-01-07T00:00:00Z"/>
</result>

3.15.18 Q18

Calculate the time needed to process an order from the orderRequest up to the shipping.

Solution in XQuery:

declare variable $seq := fn:doc("cxml.xml");

<result>{
  for sliding window $w in $seq/sequence/* 
    start  $s when $s[self::OrderRequest]
    end  $e when $e/@orderID eq  $s/@orderID and
      $e[self::ConfirmationRequest] and $e/@status eq "reject" 
      or $e[self::ShipNotice]
  where $e[self::ShipNotice]
  return 
    <timeToShip orderID="{ $s/@orderID}">{xs:dateTime($e/@date) - xs:dateTime($s/@date) }</timeToShip> 
}</result>

Expected Result:

<result>
  <timeToShip orderID="OID01">P3DT22H</timeToShip>
  <timeToShip orderID="OID03">P2DT19H</timeToShip>
</result>

3.15.19 Q19

Calculate at the moment of the shipping notification if an open request exists that can be shipped to the same address.

Solution in XQuery:

declare variable $seq := fn:doc("cxml.xml");

<result>{
  for sliding window $w in $seq/sequence/* 
    start previous $wSPrev when $wSPrev[self::OrderRequest]
    end next $wENext when $wENext/@orderID eq  $wSPrev/@orderID and
          ( $wENext[self::ConfirmationRequest] and $wENext/@status eq "reject") 
            or $wENext[self::ShipNotice]
  where $wENext[self::ShipNotice]
  return 
    <bundleWith orderId="{$wSPrev/@orderID}">{
        for sliding window $bundle in $w 
          start  $bSCur 
            when $bSCur[self::OrderRequest] and $bSCur/@shipTo eq $wSPrev/@shipTo
          end  $bECur next $bENext 
            when $bECur/@orderID eq  $bSCur/@orderID 
             and ($bECur[self::ConfirmationRequest] and $bECur/@status eq "reject") 
              or $bECur[self::ShipNotice]
          where empty($bENext)
          return $bSCur
    }</bundleWith>
}</result>

Expected Result:

<result>
  <bundleWith orderId="OID01">
    <OrderRequest billTo="ACME1" date="2006-01-02T14:00:00-00:00"
      orderID="OID03" shipTo="ACME1" total="10000" type="new">
      <Item partID="ID3" quantity="100" unitPrice="100"/>
    </OrderRequest>
  </bundleWith>
  <bundleWith orderId="OID03"/>
</result>

XQuery 1.1 Use Cases

W3C Working Draft 11 July 2008

Abstract

Status of this Document

Table of Contents

Appendices

1 Use Cases for XML Query 1.1

2 Use Case "group by": Queries which require grouping

2.1 DTD for Q1-Q6

2.2 Sample Data

2.3 DTD for Q7-Q8

2.4 Sample Data

2.5 Queries and Results

2.5.1 Q1

2.5.2 Q2

2.5.3 Q3

2.5.4 Q4

2.5.5 Q5

2.5.6 Q6

2.5.7 Q7

2.5.8 Q8

3 Use Case "Windowing": Queries which require windowing

3.1 DTD for Q1

3.2 Sample Data

3.3 DTD for Q2

3.4 Sample Data

3.5 DTD for Q3

3.6 Sample Data

3.7 DTD for Q4-Q6

3.8 Sample Data

3.9 DTD for Q7-Q13

3.10 Sample Data

3.11 DTD for Q14-Q16

3.12 Sample Data

3.13 DTD for Q17-Q19

3.14 Sample Data

3.15 Queries and Results

3.15.1 Q1

3.15.2 Q2

3.15.3 Q3

3.15.4 Q4

3.15.5 Q5

3.15.6 Q6

3.15.7 Q7

3.15.8 Q8

3.15.9 Q9

3.15.10 Q10

3.15.11 Q11

3.15.12 Q12

3.15.13 Q13

3.15.14 Q14

3.15.15 Q15

3.15.16 Q16

3.15.17 Q17

3.15.18 Q18

3.15.19 Q19

A Acknowledgements (Non-Normative)

B Change Log (Non-Normative)

B.1 30 January 2008

C References (Non-Normative)