Filter
Top Products
46 The XML Files: Development of XML/XSL Applications Using WebSphere Studio
RETURN $p/pno SORTBY(.)
Grouping in XQuery
Find the part number and average price for parts that have at least three
suppliers.
SQL version:
SELECT pno, avg(price) AS avgprice
FROM sp
GROUP BY pno
HAVING count(*) >= 3
ORDER BY pno;
XQuery version:
FOR $pn IN distinct(document("sp.xml")//pno)
LET $sp := document("sp.xml")//sp_tuple[pno = $pn]
WHERE count($sp) >= 3
RETURN
<well_supplied_item>
$pn,
<avgprice> avg($sp/price) </avgprice>
</well_supplied_item> SORTBY(pno)
The $pn represents an individual part number, but $sp represents a set of
records.
Joins
Joins combine data from multiple sources. We will present a simple example with
an inner join.
Return a "flat" list of supplier names and their part descriptions, in alphabetic
order:
FOR $sp IN document("sp.xml")//sp_tuple,
$p IN document("p.xml")//p_tuple[pno = $sp/pno],
$s IN document("s.xml")//s_tuple[sno = $sp/sno]
RETURN
<sp_pair>
$s/sname ,
$p/descrip
</sp_pair> SORTBY (sname, descrip)
In conclusion, XQuery is designed to support many types of queries. The more
versatile the Xquery is, the more it will provide usability for XML for applications.