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, [email protected]. 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):
The sales-records.xml document follows this DTD:
The stores.xml document is valid to this DTD:
The content of products.xml is:
broiler kitchen 100 70 toaster kitchen 30 10 blender kitchen 50 25 socks clothes 5 2 shirt clothes 10 3
The content of sales-records.xml is:
broiler 1 20 toaster 2 100 toaster 2 50 toaster 3 50 blender 3 100 blender 3 150 socks 1 500 socks 2 10 shirt 3 10
The content of stores.xml is:
1 CA 2 CA 3 MA
Here are the contents of books.xml:
Jim Melton Alan Simon SQL:1999 Jim Melton Advanced SQL:1999 Alan Simon Strategic Database Technology Jim Melton Andrew Eisenberg Understanding SQL and Java Together Jim Melton Stephen Buxton Querying XML
Group sales by product, list name and total quantity of each product.
Solution in XQuery:
{ for $sales in doc("sales-records.xml")/*/record let $pname := $sales/product-name group by $pname order by $pname return { sum($sales/qty) } }
Expected Result:
250 20 10 510 200
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:
{ 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 {$state, $category} }{sum($sales/qty)}
Expected Result:
CA clothes 510 CA kitchen 170 MA clothes 10 MA kitchen 300
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:
{ 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 {$state, $category} }{sum($revenue)}
Expected Result:
CA clothes 2550 CA kitchen 6500 MA clothes 100 MA kitchen 14000
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:
{ for $store in doc("stores.xml")/*/store let $state := $store/state group by $state order by $state return { for $product in doc("products.xml")/*/product let $category := $product/category group by $category order by $category return }{ 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 }}
Expected 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:
{ for $sales in doc("sales-records.xml")/*/record let $storeno := $sales/store-number group by $storeno order by $storeno return { for $s in $sales order by xs:int($s/qty) descending return }}
Expected 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:
{ 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 }
Expected 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:
{ for $book in doc("books.xml")/*/book for $author in $book/author group by $author order by $author return { for $b in $book order by $b/title return }{fn:data($b/title)} }
Expected Result:
SQL:1999 Strategic Database Technology Understanding SQL and Java Together Advanced SQL:1999 Querying XML SQL:1999 Understanding SQL and Java Together Querying XML
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:
{ for $book in doc("books.xml")/*/book let $author-list := fn:string-join($book/author, ', ') group by $author-list order by $author-list return { for $b in $book order by $b/title return }{fn:data($b/title)} }
Expected Result:
Strategic Database Technology Advanced SQL:1999 SQL:1999 Understanding SQL and Java Together Querying XML
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.
The content of term_def_list.xml is:
XML Extensible Markup Language XSLT XSL Transformations A language for transforming XML A specification produced by W3C
The content of person_events.xml is:
Anton in Barbara in Clara in Clara out Barbara out Anton out Anton in Clara in Anton out Clara out
Q14 - Q16 use as input document a slightly modified RSS document named rss.xml, with the following DTD :
The main difference to RSS 2.0 how dates are expressed in the data. The content of rss.xml is:
DBIS RSS http://www.dbis.ethz.chThe windowing dummy RSS. en-us Why use cases are important Part 1. Workshop [email protected] 2003-06-03T09:00:00 Why use cases are important Part 2. Workshop [email protected] 2003-06-03T09:00:00 Why use cases are important Part 3. Workshop [email protected] 2003-06-03T10:00:00 Extending XQuery with Window Functions Talk [email protected] 2003-06-03T11:00:00 XQueryP: A new programming language is born Talk [email protected] 2003-06-03T12:00:00 Why use cases are annoying to write. Talk [email protected] 2003-06-04T10:00:00
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:
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");
| {data($i)} | }
Expected Result:
| Green | Pink | Lilac |
| Turquoise | Peach | Opal |
| Champagne |
Convert a structure with implicit sections to a structure with explicit sections.
Solution in XQuery:
declare variable $seq := fn:doc("head_para.xml");
{
for tumbling window $w in $seq/body/*
start previous $s when $s[self::h2]
end at $e when $e[self::h2]
return
{
for $x in $w
return
{data($x)}
}
}
Expected Result:
para1 para2 heading2 para3 para4 para5
Within a glossary in HTML, a defined term
Solution in XQuery:
declare variable $seq := fn:doc("term_def_list.xml");
{
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
{
$w
}
}
Expected Result:
XML Extensible Markup Language XSLT XSL Transformations A language for transforming XML A specification produced by W3C
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 Outlier detected. Event id:{data($next/@time)}
Expected Result:
Outlier detected. Event id:5
Notify when Barbara enters the building within 1 hour after Anton
Solution in XQuery:
declare variable $seq := fn:doc("person_events.xml");
{
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
Barbara: Anton arrived 1h ago
}
Expected Result:
Barbara: Anton arrived 1h ago
Measure the working time of each person
Solution in XQuery:
declare variable $seq := fn:doc("person_events.xml");
{
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
{$s/person}
}
Expected Result:
Clara Barbara Anton Clara Clara Clara Anton Clara
Measure the overall working time for each person.
Solution in XQuery:
declare variable $seq := fn:doc("person_events.xml");
{
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
{$person}
}
Expected Result:
Barbara Anton Clara
Display a warning if Barbara does not come to work.
Solution in XQuery:
declare variable $seq := fn:doc("person_events.xml");
{
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 Barbara did not come to work
}
Expected Result:
Barbara did not come to work
Identify every person who arrives at least 15 minutes earlier than Clara.
Solution in XQuery:
declare variable $seq := fn:doc("person_events.xml");
{
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
{
distinct-values(for $y in $w
where (xs:dateTime($y/@time) + xs:dayTimeDuration("PT15M") ) lt xs:dateTime($x/@time)
return $y/person)
}
}
Expected Result:
Anton Clara Barbara Anton Clara
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");
{
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
Anton and Barbara just arrived
}
Expected Result:
Anton and Barbara just arrived
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");
{
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
{fn:data($s/person)} is suspicious
}
Expected Result:
Clara is suspicious
Find all annoying authors who have posted three consecutive items in the RSS feed.
Solution in XQuery:
declare variable $rssfeed := fn:doc("rss.xml");
{
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 {
$w[1]/author
}
}
Expected 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");
{
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
-
{xs:date(xs:dateTime($s_curr/pubDate))}
{ for $item in $w
where fn:contains( xs:string($item/title), 'XQuery')
return $item/title }
}
Expected Result:
2003-06-03 Extending XQuery with Window Functions XQueryP: A new programming language is born 2003-06-04
Every day, provide a summary of the RSS feed grouped by author.
Solution in XQuery:
declare variable $rssfeed := fn:doc("rss.xml");
{
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
-
{xs:date(xs:dateTime($s_curr/pubDate))}
{ for $a in fn:distinct-values($w/author)
return
{ $w[author eq $a]/title }
}
}
Expected Result:
2003-06-03 Why use cases are important Part 1. Why use cases are important Part 2. Why use cases are important Part 3. Extending XQuery with Window Functions XQueryP: A new programming language is born 2003-06-04 Why use cases are annoying to write.
At the end of a day, list the most valuable customers.
Solution in XQuery:
declare variable $seq := fn:doc("cxml.xml");
{
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
{
let $companies := for $x in distinct-values($w/@billTo )
return {sum($w[@billTo eq $x]/@total)}
let $max := max($companies)
for $company in $companies
where $company eq xs:untypedAtomic($max)
return $company
}
}
Expected Result:
1100 10000 100
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");
{
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
{xs:dateTime($e/@date) - xs:dateTime($s/@date) }
}
Expected Result:
P3DT22H P2DT19H
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");
{
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
{
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
}
}
Expected 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.