Chapter 26: XQuery for SQL Users

Please note that the book contains many inline examples and informal tables that are not provided here.

Example 26-1. Product catalog document (catalog2.xml)

<catalog>
  <product dept="WMN">
    <number>557</number>
    <name>Fleece Pullover</name>
  </product>
  <product dept="ACC">
    <number>563</number>
    <name>Floppy Sun Hat</name>
  </product>
  <product dept="ACC">
    <number>443</number>
    <name>Deluxe Travel Bag</name>
  </product>
  <product dept="MEN">
    <number>784</number>
    <name>Cotton Dress Shirt</name>
    <desc>Our favorite shirt!</desc>
  </product>
</catalog>

Example 26-2. Distinctness on multiple values

Query

for $d in distinct-values(doc("catalog2.xml")//product/@dept),
    $n in distinct-values(doc("catalog2.xml")//product[@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 26-3. Two-way join in XQuery

Query

for $item in doc("order.xml")//item,
    $prod in doc("catalog2.xml")//product
where $item/@num = $prod/number
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 26-4. Grouping in XQuery

Query

xquery version "3.0";
for $prod in doc("catalog2.xml")//product
group by $d := $prod/@dept
return <result dept="{$d}" count="{count($prod)}"/>

Results

<result code="ACC" count="2"/>
<result code="MEN" count="1"/>
<result code="WMN" count="1"/>

Example 26-5. SQL/XML query