FLWOR examples

These examples show to how to use FLWOR expressions in complete queries to perform joins, grouping, and aggregation.

FLWOR expression that joins XML data

The following query joins XML data from the PRODUCT and PURCHASEORDER tables in the SAMPLE database to list the names of products ordered in purchase orders placed in 2005.

Because the elements in both the product documents and the PurchaseOrder documents are in the same namespace, the query begins by declaring a default namespace so that the element names in the query do not need prefixes. The for clause iterates over the PURCHASEORDER.PORDER column, specifically for purchase orders with OrderDate attribute value that starts with "2005". For each purchase order, the let clause assigns the partid values to the $parts variable. The return clause then lists the names of the products that are included in the purchase order.

for $po in db2-fn:xmlcolumn('PURCHASEORDER.PORDER')
  /PurchaseOrder[fn:starts-with(@OrderDate, "2005")]
let $parts := $po/item/partid
return
<ProductList PoNum = "{$po/@PoNum}">
    { db2-fn:xmlcolumn('PRODUCT.DESCRIPTION')
    /product[@pid = $parts]/description/name }
</ProductList>

The query returns the following result:

<ProductList PoNum="5001">
    <name>Snow Shovel, Deluxe 24 inch</name>
    <name>Snow Shovel, Super Deluxe 26 inch</name>
    <name>Ice Scraper, Windshield 4 inch</name>
</ProductList>
<ProductList PoNum="5003">
    <name>Snow Shovel, Basic 22 inch</name>
</ProductList>
<ProductList PoNum="5004">
    <name>Snow Shovel, Basic 22 inch</name>
    <name>Snow Shovel, Super Deluxe 26 inch</name>
</ProductList>

FLWOR expression that groups elements

The following query groups customer names in the CUSTOMER table of the SAMPLE database by city. The for clause iterates over the customerinfo documents and binds each city element to the variable $city. For each city, the let clause binds the variable $cust-names to an unordered list of all the customer names in that city. The query returns city elements that each contain the name of a city and the nested name elements of all of the customers who live in that city.

for $city in fn:distinct-values(db2-fn:xmlcolumn('CUSTOMER.INFO')
    /customerinfo/addr/city)
let $cust-names := db2-fn:xmlcolumn('CUSTOMER.INFO')
    /customerinfo/name[../addr/city = $city]
order by $city
return <city>{$city, $cust-names} </city>

The query returns the following result:

<city>Aurora
    <name>Robert Shoemaker</name>
</city>
<city>Markham
    <name>Kathy Smith</name>
    <name>Jim Noodle</name>
</city>
<city>Toronto
    <name>Kathy Smith</name>
    <name>Matt Foreman</name>
    <name>Larry Menard</name>
</city>

FLWOR expression that aggregates data

The following query returns the total revenue generated by each purchase order in 2005 and creates an HTML report.

The query iterates over each PurchaseOrder element with an order date in 2005 and binds the element to the variable $po in the for clause. The path expression $po/item/ then moves the context position to each item element within a PurchaseOrder element. The nested expression (price * quantity) determines the total revenue for that item. The fn:sum function adds the resulting sequence of total revenue for each item. The let clause binds the result of the fn:sum function to the variable $revenue. The order by clause sorts the results by total revenue for each purchase order. Finally, the return clause creates a row in the report table for each purchase order.

<html>
<body>
<h1>PO totals</h1>
<table>
<thead>
    <tr>
        <th>PO Number</th>
        <th>Status</th>
        <th>Revenue</th>
    </tr>
</thead>
<tbody>{
    for $po in db2-fn:xmlcolumn('PURCHASEORDER.PORDER')/
      PurchaseOrder[fn:starts-with(@OrderDate, "2005")]
    let $revenue := sum($po/item/(price * quantity))
    order by $revenue descending
    return
      <tr>
          <td>{string($po/@PoNum)}</td>
          <td>{string($po/@Status)}</td>
          <td>{$revenue}</td>
      </tr>
}
</tbody>
</table>
</body>
</html>

The query returns the following result:

<html>
<body>
<h1>PO totals</h1>
<table>
<thead>
    <tr>
        <th>PO Number</th>
        <th>Status</th>
        <th>Revenue</th>
    </tr>
</thead>
<tbody>
    <tr>
        <td>5004</td>
        <td>Shipped</td>
        <td>139.94</td>
    </tr>
    <tr>
        <td>5001</td>
        <td>Shipped</td>
        <td>123.96</td>
    </tr>
    <tr>
        <td>5003</td>
        <td>UnShipped</td>
        <td>9.99</td>
    </tr>
</tbody>
</table>
</body>
</html>

When viewed in a browser, the query output would look similar to the following table:

Table 1. PO totals
PO Number	Status	Revenue
5004	Shipped	139.94
5001	Shipped	123.96
5003	Unshipped	9.99

FLWOR expression that updates XML data

The following example uses the CUSTOMER table from the Db2 SAMPLE database. In the CUSTOMER table, the XML column INFO contains customer address and phone information.

The transform expression creates a copy of an XML document containing customer information. In the modify clause, the FLWOR expression and the rename expression change all instances of the node name phone to the name phonenumber:

xquery 
transform 
  copy $mycust := db2-fn:sqlquery('select info from customer where cid = 1003')
  modify 
    for $phone in $mycust/customerinfo/phone
    return
      do rename $phone as "phonenumber"
  return $mycust

When run against the SAMPLE database, the expression changes the node name phone to phonenumber and returns the following result:

<customerinfo Cid="1003">
  <name>Robert Shoemaker</name>
  <addr country="Canada">
    <street>1596 Baseline</street>
    <city>Aurora</city>
    <prov-state>Ontario</prov-state>
    <pcode-zip>N8X 7F8</pcode-zip>
  </addr>
  <phonenumber type="work">905-555-7258</phonenumber>
  <phonenumber type="home">416-555-2937</phonenumber>
  <phonenumber type="cell">905-555-8743</phonenumber>
  <phonenumber type="cottage">613-555-3278</phonenumber>
</customerinfo>