XQuery Examples: Selecting and Joining Using FLWORs

Example 6-1. FLWOR

for $prod in doc("catalog.xml")//product
let $prodDept := $prod/@dept
where $prodDept = "ACC" or $prodDept = "WMN"
return $prod/name

Example 6-2. Using a range expression

Query

for $i in 1 to 3
return <oneEval>{$i}</oneEval>

Results

<oneEval>1</oneEval>
<oneEval>2</oneEval>
<oneEval>3</oneEval>

Example 6-3. Multiple for clauses

Query

for $i in (1, 2)
for $j in ("a", "b")
return <oneEval>i is {$i} and j is {$j}</oneEval>

Results

<oneEval>i is 1 and j is a</oneEval>
<oneEval>i is 1 and j is b</oneEval>
<oneEval>i is 2 and j is a</oneEval>
<oneEval>i is 2 and j is b</oneEval>

Example 6-4. Multiple variable bindings in one for clause

for $i in (1, 2), $j in ("a", "b")
return <oneEval>i is {$i} and j is {$j}</oneEval>

Example 6-5. Using a let clause with a range expression

Query

let $i := (1 to 3)
return <oneEval>{$i}</oneEval>

Results

<oneEval>1 2 3</oneEval>

Example 6-6. Intermingled for and let clauses

let $doc := doc("catalog.xml")
for $prod in $doc//product
let $prodDept := $prod/@dept
let $prodName := $prod/name
where $prodDept = "ACC" or $prodDept = "WMN"
return $prodName

Example 6-7. A where clause with multiple expressions

for $prod in doc("catalog.xml")//product
let $prodDept := $prod/@dept
where $prod/number > 100
      and starts-with($prod/name, "F")
      and exists($prod/colorChoices)
      and ($prodDept = "ACC" or $prodDept = "WMN")
return $prod

Example 6-8. Quantified expression using the some keyword

some $dept in doc("catalog.xml")//product/@dept
satisfies ($dept = "ACC")

Example 6-9. Quantified expression using the every keyword

every $dept in doc("catalog.xml")//product/@dept
satisfies ($dept = "ACC")

Example 6-10. Combining the not function with a quantified expression

not(some $dept in doc("catalog.xml")//product/@dept
      satisfies ($dept = "ACC"))

Example 6-11. Binding multiple variables in a quantified expression

some $i in (1 to 3), $j in (10, 11)
  satisfies $j - $i = 7

Example 6-12. Distinctness on a combination of values

Query

let $prods := doc("catalog.xml")//product
for $d in distinct-values($prods/@dept),
    $n in distinct-values($prods[@dept = $d]/number)
return <result dept="{$d}" number="{$n}"/>

Results

<result dept="WMN" number="557"/>
<result dept="ACC" number="563"/>
<result dept="ACC" number="443"/>
<result dept="MEN" number="784"/>

Example 6-13. Two-way join in a predicate

Query

for $item in doc("order.xml")//item,
    $prod in doc("catalog.xml")//product[number = $item/@num]
return <item num="{$item/@num}"
             name="{$prod/name}"
             quan="{$item/@quantity}"/>

Results

<item num="557" name="Fleece Pullover" quan="1"/>
<item num="563" name="Floppy Sun Hat" quan="1"/>
<item num="443" name="Deluxe Travel Bag" quan="2"/>
<item num="784" name="Cotton Dress Shirt" quan="1"/>
<item num="784" name="Cotton Dress Shirt" quan="1"/>
<item num="557" name="Fleece Pullover" quan="1"/>

Example 6-14. Two-way join in a where clause

for $item in doc("order.xml")//item,
    $prod in doc("catalog.xml")//product
where $item/@num = $prod/number
return <item num="{$item/@num}"
             name="{$prod/name}"
             quan="{$item/@quantity}"/>

Example 6-15. Three-way join in a where clause

Query

for $item in doc("order.xml")//item,
    $prod in doc("catalog.xml")//product,
    $price in doc("prices.xml")//prod
where $item/@num = $prod/number and $prod/number = $price/@num
return <item num="{$item/@num}"
             name="{$prod/name}"
             price="{$price/price}"/>

Results

<item num="557" name="Fleece Pullover" price="29.99"/>
<item num="563" name="Floppy Sun Hat" price="69.99"/>
<item num="443" name="Deluxe Travel Bag" price="39.99"/>
<item num="557" name="Fleece Pullover" price="29.99"/>

Example 6-16. Outer join

Query

for $prod in doc("catalog.xml")//product
return <product number="{$prod/number}">{
                attribute price
                   {for $price in doc("prices.xml")//prod
                    where $prod/number = $price/@num
                    return $price/price}
  }</product>

Results

<product number="557" price="29.99"/>
<product number="563" price="69.99"/>
<product number="443" price="39.99"/>
<product number="784" price=""/>

Example 6-17. Outer join with allowing empty

xquery version "3.0";
for $prod in doc("catalog.xml")//product
for $price allowing empty
    in doc("prices.xml")//prices/priceList/prod[@num = $prod/number]
return <product number="{$prod/number}" price="{$price/price}"/>

Chapter 6: Selecting and Joining Using FLWORs