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";
count(
doc("ipo.xml")//shipTo[. instance of element(*, ipo:UKAddress)]
)
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 called "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>
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/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";
import schema namespace zips = "http://www.example.com/zips";
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
};
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 called "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";
import schema namespace pst = "http://www.example.com/postals";
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)
};
1.9.4.4 Q4
Return purchase orders with an erroneous postal code or zip code (depending on whether it is a UK Address or a US Address).
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO";
import schema namespace pst="http://www.example.com/postals";
import schema namespace zips="http://www.example.com/zips";
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()
};
for $p in doc("ipo.xml")//element(ipo:purchaseOrder)
where not( local:address-ok($p/shipTo) and local:address-ok($p/billTo))
return $p
This query calls the functions defined in Q2 and Q3.
Note that the function 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
Write a query that returns purchase orders where the name in the shipping address does not match the name in the billing address.
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO";
declare function local:names-match( $s as element(shipTo),
$b as element(billTo) )
as xs:boolean
{
$s/name = $b/name
};
for $p in doc("ipo.xml")//element(ipo:purchaseOrder)
where not( local:names-match( $p/shipTo, $p/billTo ) )
return $p
In this function, note that the function uses the element names rather than the type names to specify the parameters. The query should return an error if the function is called with two ipo:billTo elements by mistake, but it is not a mistake to have the shipping address and billing address have different types.
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
Find purchase orders where the billing address is not a USAddress, but at least one price is a USPrice.
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO";
for $p in doc("ipo.xml")//element(ipo:purchaseOrder),
$b in $p/billTo
where not( $b instance of element(*, ipo:USAddress))
and exists( $p//USPrice )
return $p
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.
The example in the XML Schema Primer uses the following example for substitution groups:
<element name="comment" type="string"/>
. . .
<element name="shipComment" type="string"
substitutionGroup="ipo:comment"/>
<element name="customerComment" type="string"
substitutionGroup="ipo:comment"/>
The example in the XML Schema Primer provides the following sample data to illustrate 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";
declare function local:comment-text($c as 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:
for $p in doc("ipo.xml")//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";
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//element(ipo:comment)
Note that 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";
declare function local:comments-for-element( $e as element() )
as element(ipo:comment)*
{
$e/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.
This query assumes that "http://www.usa-deadbeats.com" maintains a database of 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>0</creditrating>
</row>
</deadbeats>
Solution in XQuery:
import schema namespace ipo="http://www.example.com/IPO";
declare function local:deadbeat( $b as element(billTo, ipo:USAddress) )
as xs:boolean
{
$b/name = doc("http://www.usa-deadbeats.com/current")/deadbeats/row/name
};
for $p in doc("ipo.xml")//element(ipo:purchaseOrder)
where $p/@orderDate = date("2004-12-01")
and local:deadbeat( $p/element(billTo) )
return <warning>{ $p/billTo/name, "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.
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";
declare function c:total-price( $i as element(ipo: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";
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//ipo: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 US purchase orders, not on the international purchase report used in previous queries. In the XML Schema Primer, these US purchase orders are defined without a namespace. Here is an example of such a purchase order, taken from the XML Schema Primer:
<?xml version="1.0"?>
<purchaseOrder orderDate="1999-10-20">
<shipTo country="US">
<name>Alice Smith</name>
<street>123 Maple Street</street>
<city>Mill Valley</city>
<state>CA</state>
<zip>90952</zip>
</shipTo>
<billTo country="US">
<name>Robert Smith</name>
<street>8 Oak Avenue</street>
<city>Old Town</city>
<state>PA</state>
<zip>95819</zip>
</billTo>
<comment>Hurry, my lawn is going wild!</comment>
<items>
<item partNum="872-AA">
<productName>Lawnmower</productName>
<quantity>1</quantity>
<USPrice>148.95</USPrice>
<comment>Confirm this is electric</comment>
</item>
<item partNum="926-AA">
<productName>Baby Monitor</productName>
<quantity>1</quantity>
<USPrice>39.98</USPrice>
<shipDate>1999-05-21</shipDate>
</item>
</items>
</purchaseOrder>
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 := collection("orders")/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>