1.9.4 Queries
1.9.4.1 Q1
Count the invoices shipped to the United
Kingdom.
Solution in XQuery:
import schema namespace ipo = "http://www.example.com/IPO" at "ipo.xsd";
count(
doc("ipo.xml")//shipTo[. instance of element(*, ipo:UKAddress)]
)
Expected Result:
In this dataset, the data for an address does not
contain the name of the country, and the name of the
shipTo element is the same regardless of the country to
which items are shipped. Only the types allow us to
identify UK addresses - in the schema, there is one
address type for UK addresses and another for US
addresses, both derived from a common base class. In
the above query, we use the UKAddress type to identify
invoices shipped to the UK.
1.9.4.2 Q2
Write a function that tests an American address to
check if it has the right zip code.
In our solution, we will assume zip code data is
stored in a file named "zips.xml", which looks like
this.
<zips xmlns="http://www.example.com/zips">
<row>
<city>Old Town</city>
<state>PA</state>
<zip>95819</zip>
</row>
<row>
<city>Durham</city>
<state>NC</state>
<zip>27701</zip>
</row>
<row>
<city>Durham</city>
<state>NC</state>
<zip>27703</zip>
</row>
</zips>
The corresponding schema document is named
"zips.xsd":
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified"
targetNamespace="http://www.example.com/zips"
xmlns:zips="http://www.example.com/zips">
<xs:element name="zips">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" ref="zips:row"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="row">
<xs:complexType>
<xs:sequence>
<xs:element ref="zips:city"/>
<xs:element ref="zips:state"/>
<xs:element ref="zips:zip"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="city" type="xs:string"/>
<xs:element name="state" type="xs:string"/>
<xs:element name="zip" type="xs:integer"/>
</xs:schema>
Solution in XQuery:
module namespace z="http://www.example.com/xq/zips";
import schema namespace ipo = "http://www.example.com/IPO" at "ipo.xsd";
import schema namespace zips = "http://www.example.com/zips" at "zips.xsd";
declare function z:zip-ok($a as element(*, ipo:USAddress))
as xs:boolean
{
some $i in doc("zips.xml")/zips:zips/element(zips:row)
satisfies $i/zips:city = $a/city
and $i/zips:state = $a/state
and $i/zips:zip = $a/zip
};
Note:
This is not a complete query, it is a function
that is meant to be called in a query. We will use
this function in Q4.
An attempt to call this function with an element of
the wrong address type raises an error. For instance,
you can not call z:zip-ok() with an element of type
UKAddress.
Note that the parameter for this function specifies
the type rather than the element name, since it is
written to be used with any element that has the proper
address type - for instance, in our sample schema,
'billTo' and 'shipTo' are two different elements which
may both have the USAddress type.
1.9.4.3 Q3
Write a function that tests a UK address to see if
it has the right postal code.
For England, we store the information needed to test
postal codes in a file named "postals.xml", which looks
like this:
<postals xmlns="http://www.example.com/postals">
<row>
<city>Cambridge</city>
<prefix>CB</prefix>
</row>
<row>
<city>Oxford</city>
<prefix>OX</prefix>
</row>
</postals>
Here is the schema for the above file.
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified"
targetNamespace="http://www.example.com/postals"
xmlns:postals="http://www.example.com/postals">
<xs:element name="postals">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" ref="postals:row"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="row">
<xs:complexType>
<xs:sequence>
<xs:element ref="postals:city"/>
<xs:element ref="postals:prefix"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="city" type="xs:string"/>
<xs:element name="prefix" type="xs:string"/>
</xs:schema>
Solution in XQuery:
module namespace p="http://www.example.com/xq/postals";
import schema namespace ipo = "http://www.example.com/IPO" at "ipo.xsd";
import schema namespace pst = "http://www.example.com/postals" at "postals.xsd";
declare function p:postal-ok($a as element(*, ipo:UKAddress))
as xs:boolean
{
some $i in doc("postals.xml")/pst:postals/element(pst:row)
satisfies $i/pst:city = $a/city
and starts-with($a/postcode, $i/pst:prefix)
};
Note:
This is not a complete query, it is a function
that is meant to be called in a query. We will use
this function in Q4.
1.9.4.4 Q4
Determine whether the postal code or zip code for a
purchase order is right.
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
import schema namespace pst="http://www.example.com/postals" at "postals.xsd";
import schema namespace zips="http://www.example.com/zips" at "zips.xsd";
import module namespace zok="http://www.example.com/xq/zips";
import module namespace pok="http://www.example.com/xq/postals";
declare function local:address-ok($a as element(*, ipo:Address))
as xs:boolean
{
typeswitch ($a)
case $zip as element(*, ipo:USAddress)
return zok:zip-ok($zip)
case $postal as element(*, ipo:UKAddress )
return pok:postal-ok($postal)
default return false()
};
let $shipTo := doc("ipo.xml")/element(ipo:purchaseOrder)/shipTo
let $billTo := doc("ipo.xml")/element(ipo:purchaseOrder)/billTo
return local:address-ok($shipTo) and local:address-ok($billTo)
Expected Result:
This query calls the functions defined in Q2 and
Q3.
Note that the function local:address-ok() accepts
any element whose type is ipo:Address, which is the
base type for both ipo:UKAddress and ipo:USAddress.
Note also that this function uses a typeswitch to
select the appropriate function to test American or
British addresses. This can be considered a primitive
form of polymorphism.
1.9.4.5 Q5
Determine whether the shipping address matches the
billing address.
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
declare function local:names-match( $s as element(shipTo, ipo:Address),
$b as element(billTo, ipo:Address))
as xs:boolean
{
$s/name = $b/name
};
let $p := doc("ipo.xml")/element(ipo:purchaseOrder)
return local:names-match( $p/shipTo, $p/billTo )
Expected Result:
In this function, note that the function specifies
both the element names and the type names for its
parameters.
Note also that the schema says both of these
elements are local elements, defined only within a
purchase order. The element test matches an element
with a given name even if that element is locally
declared.
1.9.4.6 Q6
Determine whether the invoice has a USAddress and
gives all prices as USPrices.
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
let $p := doc("ipo.xml")//element(ipo:purchaseOrder)
let $billTo := $p/billTo
let $shipTo := $p/shipTo
return
if ($billTo instance of element(*, ipo:USAddress))
then every $i in $p/items/item
satisfies (exists($i/USPrice))
else false()
Expected Result:
Addresses are part of a type hierarchy, and the
element name, ipo:shipTo, does not tell us whether an
address is a US price or not, so we have to test the
type.
This example is rather contrived, since the schema
specifies that all prices are USPrice elements.
Nevertheless, it illustrates the ability to easily
combine information derived from type information with
information derived from structure.
1.9.4.7 Q7
Write a function that returns the text of a comment.
Call this function for each shipping comment found in
an item shipped to Helen Zoe on the date
1999-12-01.
Our source schema models comments with the following
substitution groups:
<element name="comment" type="string"/>
. . .
<element name="shipComment" type="string"
substitutionGroup="ipo:comment"/>
<element name="customerComment" type="string"
substitutionGroup="ipo:comment"/>
The following sample data contains instances of
these substitution groups:
<items>
<item partNum="833-AA">
<productName>Lapis necklace</productName>
<quantity>1</quantity>
<USPrice>99.95</USPrice>
<ipo:shipComment>
Use gold wrap if possible
</ipo:shipComment>
<ipo:customerComment>
Want this for the holidays!
</ipo:customerComment>
<shipDate>1999-12-05</shipDate>
</item>
</items>
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
declare function local:comment-text($c as schema-element(ipo:comment))
as xs:string
{
xs:string( $c )
};
for $p in doc("ipo.xml")//element(ipo:purchaseOrder),
$t in local:comment-text( $p//ipo:shipComment )
where $p/shipTo/name="Helen Zoe"
and $p/@orderDate = xs:date("1999-12-01")
return $t
In this query, the function specifies ipo:comment as
the name of the element, but any element in the
substitution group of ipo:comment may also be passed to
this function. That means that we can call the same
function for ipo:shipComment elements or
ipo:customerComment elements - for instance, the
following query also succeeds:
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
declare function local:comment-text($c as schema-element(ipo:comment))
as xs:string
{
xs:string( $c )
};
for $p in doc("ipo.xml")/schema-element(ipo:purchaseOrder)
where $p/shipTo/name="Helen Zoe"
and $p/@orderDate = xs:date("1999-12-01")
return local:comment-text( $p//ipo:customerComment )
1.9.4.8 Q8
Find all comments found in an item shipped to Helen
Zoe on the date 1999-12-01, including all elements in
the substitution group for ipo:comment.
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
for $p in doc("ipo.xml")//element(ipo:purchaseOrder)
where $p/shipTo/name="Helen Zoe"
and $p/@orderDate = xs:date("1999-12-01")
return $p//schema-element(ipo:comment)
Note that schema-element(ipo:comment)
matches any valid element in the substitution group of
ipo:comment.
1.9.4.9 Q9
Write a function that returns all comments found on
an element, whether an item element or some other
element that may have a comment.
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
declare function local:comments-for-element( $e as element() )
as element(ipo:comment)*
{
$e/schema-element(ipo:comment)
};
for $p in doc("ipo.xml")//element(ipo:purchaseOrder)
where $p/shipTo/name="Helen Zoe"
and $p/@orderDate = xs:date("1999-12-01")
return
<comments name="{$p/shipTo/name}" date="{$p/@orderDate}">
{
local:comments-for-element( $p )
}
{
for $i in $p//item
return local:comments-for-element( $i )
}
</comments>
In this schema, comments can occur on either a
purchase order or on an item. In a more complete
schema, they could presumably occur in other areas as
well. This function returns all comments found on an
element, regardless of the name of the element,
illustrating the need to write functions that can
accept any element as a parameter.
1.9.4.10 Q10
Write a function that determines whether the person
listed in a billTo element is known to be a deadbeat,
using a US database.
Note:
In American slang, a "deadbeat" is a person who
fails to meet a financial obligation.
This query assumes that "deadbeats.xml" lists the
names deadbeats in the following format:
<?xml version="1.0"?>
<deadbeats>
<row>
<name>Dick Dastardly</name>
<creditrating>0</creditrating>
</row>
<row>
<name>Peter Doubt</name>
<creditrating>1</creditrating>
</row>
<row>
<name>Robert Smith</name>
<creditrating>0</creditrating>
</row>
</deadbeats>
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
declare function local:deadbeat( $b as element(billTo, ipo:USAddress) )
as xs:boolean
{
$b/name = doc("deadbeats.xml")/deadbeats/row/name
};
for $p in doc("ipo.xml")//element(ipo:purchaseOrder)
where local:deadbeat( $p/element(billTo) )
return <warning>{ string($p/billTo/name) } is a known deadbeat!</warning>
Expected Output:
<warning>Robert Smith is a known deadbeat!</warning>
Note that this function specifies both the element
name and the type. The element name is specified
because we do not want to embarrass recipients of gifts
by calling this function for the shipping address by
mistake. The type is specified because we would need to
use a different database to identify deadbeats in other
countries.
Also note that the XML file in this example has no
schema. We assume that the processor omits validation
or does lax validation.
1.9.4.11 Q11
Write a function that computes the total price for a
sequence of item elements.
Solution in XQuery:
module namespace c="http://www.example.com/calc";
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
declare function c:total-price( $i as element(item)* )
as xs:decimal
{
let $subtotals := for $s in $i return $s/quantity * $s/USPrice
return sum( $subtotals )
};
Here is a query that calls the function we just
defined to get the total for an invoice (before
calculating taxes and shipping charges):
import schema namespace ipo="http://www.example.com/IPO" at "ipo.xsd";
import module namespace calc = "http://www.example.com/calc";
for $p in doc("ipo.xml")//element(ipo:purchaseOrder)
where $p/shipTo/name="Helen Zoe"
and $p/@orderDate = xs:date("1999-12-01")
return calc:total-price($p//item)
This query illustrates the need to be able to pass a
sequence as a parameter to a function.
If the input document contains more than one
purchase order for the given date and person, a total
will be computed for all purchase orders.
1.9.4.12 Q12
In [XMLSchema0], a
Quarterly Report is created, summarizing the types of
products that have been billed on a per region basis.
It creates the following sample report:
<purchaseReport
xmlns="http://www.example.com/Report"
period="P3M" periodEnding="1999-12-31">
<regions>
<zip code="95819">
<part number="872-AA" quantity="1"/>
<part number="926-AA" quantity="1"/>
<part number="833-AA" quantity="1"/>
<part number="455-BX" quantity="1"/>
</zip>
<zip code="63143">
<part number="455-BX" quantity="4"/>
</zip>
</regions>
<parts>
<part number="872-AA">Lawnmower</part>
<part number="926-AA">Baby Monitor</part>
<part number="833-AA">Lapis Necklace</part>
<part number="455-BX">Sturdy Shelves</part>
</parts>
</purchaseReport>
This is the schema given for the above report:
<schema targetNamespace="http://www.example.com/Report"
xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:r="http://www.example.com/Report"
xmlns:xipo="http://www.example.com/IPO"
elementFormDefault="qualified">
<!-- for SKU -->
<import namespace="http://www.example.com/IPO"/>
<annotation>
<documentation xml:lang="en">
Report schema for Example.com
Copyright 2000 Example.com. All rights reserved.
</documentation>
</annotation>
<element name="purchaseReport">
<complexType>
<sequence>
<element name="regions" type="r:RegionsType">
<keyref name="dummy2" refer="r:pNumKey">
<selector xpath="r:zip/r:part"/>
<field xpath="@number"/>
</keyref>
</element>
<element name="parts" type="r:PartsType"/>
</sequence>
<attribute name="period" type="duration"/>
<attribute name="periodEnding" type="date"/>
</complexType>
<unique name="dummy1">
<selector xpath="r:regions/r:zip"/>
<field xpath="@code"/>
</unique>
<key name="pNumKey">
<selector xpath="r:parts/r:part"/>
<field xpath="@number"/>
</key>
</element>
<complexType name="RegionsType">
<sequence>
<element name="zip" maxOccurs="unbounded">
<complexType>
<sequence>
<element name="part" maxOccurs="unbounded">
<complexType>
<complexContent>
<restriction base="anyType">
<attribute name="number" type="xipo:SKU"/>
<attribute name="quantity" type="positiveInteger"/>
</restriction>
</complexContent>
</complexType>
</element>
</sequence>
<attribute name="code" type="positiveInteger"/>
</complexType>
</element>
</sequence>
</complexType>
<complexType name="PartsType">
<sequence>
<element name="part" maxOccurs="unbounded">
<complexType>
<simpleContent>
<extension base="string">
<attribute name="number" type="xipo:SKU"/>
</extension>
</simpleContent>
</complexType>
</element>
</sequence>
</complexType>
</schema>
This report, which lists products sold by zip code,
is based on the same international purchase report used
in previous queries.
Here is a query that generates the desired report
from a collection that contains US purchase orders:
declare namespace rpt="http://www.example.com/Report";
let $orders := doc('ipo.xml')/schema-element(ipo:purchaseOrder)
[@orderDate ge xs:date("1999-09-01")
and @orderDate le xs:date("1999-12-31")]
let $items := $orders/items/item
let $zips := distinct-values($orders/billTo/zip)
let $parts := distinct-values($items/@partNum)
return
<rpt:purchaseReport>
<rpt:regions>
{
for $zip in $zips
order by $zip
return
<rpt:zip code="{$zip}">
{
for $part in $parts
let $hits := $orders[ billTo/zip = $zip and items/item/@partNum = $part]
let $quantity := sum($hits//item[@partNum=$part]/quantity)
where count($hits) > 0
order by $part
return
<rpt:part number="{$part}" quantity="{$quantity}"/>
}
</rpt:zip>
}
</rpt:regions>
<rpt:parts>
{
for $part in $parts
return
<rpt:part number="{$part}">
{
string($items[@partNum = $part]/productName)
}
</rpt:part>
}
</rpt:parts>
</rpt:purchaseReport>