IBM Version 5 manual Grouping in XQuery, Joins

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.

46 The XML Files: Development of XML/XSL Applications Using WebSphere Studio