Nesting queries

In all our examples, for and let clauses contain XPath expressions. However, they may contain more complex FLWOR expressions as well, which may contain other FLWOR expressions, and so on. Recall that a valid FLWOR expression must begin with at least one for or let clause and must terminate with a return clause.

The following query retrieves the name and email address of all people that bought more than one object and outputs them sorted by name. The second let clause uses a nested FLWOR expression in order to join the IDREF-type person attribute of a buyer element of a closed auction with the ID-type id attribute of a person element:

let $doc := doc("auction.xml")
for $p in $doc/site/people/person
let $a := for $b in $doc/site/closed_auctions/closed_auction
          where $b/buyer/@person = $p/@id
          return $b
where count($a) >= 2
order by $p/name
return <person>
          {$p/name}
          {$p/emailaddress}
          <items>{count($a)}</items>
       </person>

Notice that we can avoid the nesting of for clauses using an XPath filter as follows:

let $doc := doc("auction.xml")
for $p in $doc/site/people/person
let $a := $doc/site/closed_auctions/closed_auction[buyer/@person = $p/@id]
where count($a) >= 2
order by $p/name
return <person>
          {$p/name}
          {$p/emailaddress}
          <items>{count($a)}</items>
       </person>

The next query outputs people, sorted by their income in descending order, accompanied with the annotations that they made at some closed auction. People that made no annotations are not printed (they are ruled out by the external where clause). Once again, the subquery is used to join an IDREF-type attribute with an ID-type one.

let $doc := doc("auction.xml")
for $p in $doc/site/people/person
let $a := for $b in $doc/site/closed_auctions/closed_auction
          where $b/annotation/author/@person = $p/@id
          return $b/annotation
where count($a) > 0
order by $p/profile/number(@income) descending
return <person id="{$p/@id}" income="{$p/profile/@income}">
          <annotations>{$a}</annotations>
       </person>

The next example selects people and the European items they bought (only people that bought at least one European item are printed):

let $doc := doc("auction.xml")
for $p in $doc/site/people/person
let $item := for $a in $doc/site/closed_auctions/closed_auction
             where ($a/buyer/@person = $p/@id) and $a/itemref/id(@item)/parent::europe
             return $a/itemref
where count($item) > 0
return <person id="{$p/@id}" items="{$item/@item}"/>