This document is also available in these non-normative formats: XML.
Copyright © 2008 W3C® (MIT, ERCIM, Keio), All Rights Reserved. W3C liability, trademark and document use rules apply.
This section describes the status of this document at the time of its publication. Other documents may supersede this document. A list of current W3C publications and the latest revision of this technical report can be found in the W3C technical reports index at http://www.w3.org/TR/.
This is a First Public Working Draft as described in the Process Document. It has been developed by the W3C XML Query Working Group, which is part of the XML Activity. The Working Group expects to eventually publish this document as a Working Group Note.
This document provides a number of use cases designed to evaluate XQuery 1.1, the requirements for which are specified in [XQuery 1.1 Requirements]. Organizations and individuals should review this document to ascertain whether or not adequate coverage of the requirements is provided by these use cases.
The Working Group wishes to emphasize that this draft of this Use Cases document does not address all of the requirements identified in [XQuery 1.1 Requirements]; future drafts of this document will provide use cases for additional requirements. Furthermore, the syntax used in this document has not yet been aligned with the XQuery 1.1 First Public Working Draft.
Please report errors in this document using W3C's public Bugzilla system (instructions can be found at http://www.w3.org/XML/2005/04/qt-bugzilla). If access to that system is not feasible, you may send your comments to the W3C XSLT/XPath/XQuery public comments mailing list, public-qt-comments@w3.org. It will be very helpful if you include the string “[XQuery11UC]” in the subject line of your report, whether made in Bugzilla or in email. Please use multiple Bugzilla entries (or, if necessary, multiple email messages) if you have more than one comment to make. Archives of the comments and responses are available at http://lists.w3.org/Archives/Public/public-qt-comments/.
Publication as a Working Draft does not imply endorsement by the W3C Membership. This is a draft document and may be updated, replaced or obsoleted by other documents at any time. It is inappropriate to cite this document as other than work in progress.
This document was produced by a group operating under the 5 February 2004 W3C Patent Policy. W3C maintains a public list of any patent disclosures made in connection with the deliverables of the group; that page also includes instructions for disclosing a patent. An individual who has actual knowledge of a patent which the individual believes contains Essential Claim(s) must disclose the information in accordance with section 6 of the W3C Patent Policy.
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)
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.
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.
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)>
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>
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)>
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>
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>
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>
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>
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>
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>
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>
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>
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>
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.
Q1 uses an input document named arrange_rows.xml, with the following DTD:
<!ELEMENT doc ((data+))> <!ELEMENT data (#PCDATA)>
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>
Q2 uses an input document named head_para.xml, with the following DTD:
<!ELEMENT body ((h2, p*)*)> <!ELEMENT p (#PCDATA)> <!ELEMENT h2 (#PCDATA)>
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>
Q3 uses an input document named term_def_list.xml, with the following DTD:
<!ELEMENT doc ((dt+, dd+)*)> <!ELEMENT dt (#PCDATA)> <!ELEMENT dd (#PCDATA)>
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>
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
>
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>
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)>
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>
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)>
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>
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
>
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>
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>
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>
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>
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
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
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>
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>
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>
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>
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>
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>
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>
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>
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>
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>
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>
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>
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>
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>
The Working Group thanks the following individuals for their contributions:
| Peter M. Fischer, Donald Kossmann, Rokas Tamosevicius | Use Case "windowing" |
Use case "windowing" has been previously published in [Windowing UC].
The following references are some of the works considered by the WG in deriving its use cases.