XQuery

Grouping

Queries are often written to summarize or organize information into categories. For example, suppose you want your list of items to be grouped by department. This can be accomplished using nested FLWORs, as shown in Example 7-9.

Example 7-9. Grouping by department

Query
for $d in distinct-values(doc("order.xml")//item/@dept)
let $items := doc("order.xml")//item[@dept = $d]
order by $d
return <department code="{$d}">{
         for $i in $items
         order by $i/@num
         return $i
       }</department>
Results
<department code="ACC">
  <item dept="ACC" num="443" quantity="2"/>
  <item dept="ACC" num="563" quantity="1"/>
</department>
<department code="MEN">
  <item dept="MEN" num="784" quantity="1" color="white"/>
  <item dept="MEN" num="784" quantity="1" color="gray"/>
</department>
<department code="WMN">
  <item dept="WMN" num="557" quantity="1" color="navy"/>
  <item dept="WMN" num="557" quantity="1" color="black"/>
</department>

In this example, the variable $d is iteratively bound to each of the distinct values for department code, namely WMN, ACC, and MEN. For each department, the variable $items is bound to all the items that have the particular department code $d. Because $items is bound in a let clause rather than a for clause, the entire sequence of items (for a single department) is bound to $items, not each item individually. The order by clause causes the results to be sorted by department.

The inner FLWOR is used simply to sort $items by item number. If the order of the items within a department ...