Datypic Logo
XQuery

XQuery

Priscilla Walmsley (pwalmsley@datypic.com)

ISBN: 1491915102

2nd edition, 2015, O'Reilly Media, Inc.

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
select c.number,
       xmlelement ( name "product",
                    xmlattributes (
                      c.dept as "dept",
                      c.name as "prodname",
                   ) ) as "product_as_xml"
from catalog c;
Datypic XQuery Services