FLWOR examples

You can use FLWOR expressions in complete queries to perform joins, grouping, and aggregation.

FLWOR expression that joins XML data

The following query uses an XQuery FLWOR expression to express a join.

Suppose that the PORDER column of the PURCHASEORDER table contains this data:

<ipo:purchaseOrder
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:ipo="http://www.example.com/IPO"
   orderDate="2008-12-01">
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Helen Zoe</name>
    <street>55 Eden Street</street>
    <city>San Jose</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <shipTo exportCode="1" xsi:type="ipo:UKAddress">
    <name>Joe Lee</name>
    <street>66 University Avenue</street>
    <city>Palo Alto</city>
    <state>CA</state>
    <postcode>CB1 1JR</postcode>
  </shipTo>
  <billTo xsi:type="ipo:USAddress">
    <name>Robert Smith</name>
    <street>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <items>
    <item partNum="833-AA">
      <productName>Lapis necklace</productName>
      <quantity>1</quantity>
      <USPrice>99.95</USPrice>
      <ipo:comment>Want this for the holidays!</ipo:comment>
      <shipDate>2008-12-05</shipDate>
    </item>
    <item partNum="945-ZG">
      <productName>Sapphire Bracelet</productName>
      <quantity>2</quantity>
      <USPrice>178.99</USPrice>
      <shipDate>2009-01-03</shipDate>
    </item>
  </items>
</ipo:purchaseOrder>

Also suppose that a table named STATUS has an XML column named STATUS, which has the following data:

<status>
  <statusItem>
    <name>Robert Smith</name>
    <status>Premier</status>
    <comment>Orders a lot of jewelry</comment>
    <comment>Has friends in the Silicon Valley</comment>
  </statusItem>
  <statusItem>
    <name>Jane Carmody</name>
    <status>Unreliable</status>
    <comment>Has unpaid bills</comment>
  </statusItem>
</status>

The following example shows how to use an XQuery FLWOR expression to find those purchase orders that were made by customers with Premier status.

SELECT XMLQUERY(
  'declare namespace ipo="http://www.example.com/IPO";
  for $i in $po/ipo:purchaseOrder
  return
    <premierOrders> {
      for $j in $status/status/statusItem
      where $j/name=$i/billTo/name and $j/status="Premier"
      return
      $i }
    </premierOrders>'
  PASSING T1.PORDER as "po", T2.STATUS as "status")
  FROM PURCHASEORDER T1, STATUS T2;

The query returns the following result:

<premierOrders>
  <ipo:purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:ipo="http://www.example.com/IPO" orderDate="1999-12-01">
    <shipTo exportCode="1" xsi:type="ipo:UKAddress">
      <name>Helen Zoe</name>
      <street>55 Eden Street</street>
      <city>San Jose</city>
      <state>CA</state>
      <postcode>CB1 1JR</postcode>
    </shipTo>
    <shipTo exportCode="1" xsi:type="ipo:UKAddress">
      <name>Joe Lee</name>
      <street>66 University Avenue</street>
      <city>Palo Alto</city>
      <state>CA</state>
      <postcode>CB1 1JR</postcode>
    </shipTo>
    <billTo xsi:type="ipo:USAddress">
      <name>Robert Smith</name>
      <street>8 Oak Avenue</street>
      <city>Old Town</city>
      <state>PA</state>
      <zip>95819</zip>
    </billTo>
    <items>
      <item partNum="833-AA">
        <productName>Lapis necklace</productName>
        <quantity>1</quantity>
        <USPrice>99.95</USPrice>
        <ipo:comment>Want this for the holidays!</ipo:comment>
        <shipDate>2008-12-05</shipDate>
      </item>
      <item partNum="945-ZG">
        <productName>Sapphire Bracelet</productName>
        <quantity>2</quantity>
        <USPrice>178.99</USPrice>
        <shipDate>2009-01-03</shipDate>
      </item>
    </items>
  </ipo:purchaseOrder>
</premierOrders>

FLWOR expression that uses conditional logic

The following example shows how to use XQuery to build a report of revenue for items in the PORDER column of the PURCHASEORDER table that have already shipped.

SELECT XMLQUERY(
    'let $j := (
       for $i in $po//item
       return if (xs:date($i/shipDate) <= $currentDate)
          then xs:decimal($i/USPrice)
          else 0.0 )
    return fn:sum($j)'
  PASSING T1.PORDER as "po", CURRENT DATE as "currentDate")
FROM PURCHASEORDER T1;

Suppose that the current date is 2009-01-01. The query returns the following result:

99.95