Chapter 24: XQuery for SQL users

Example 24-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 24-2. Distinctness on a combination of values

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}"/>

Example 24-3. Two-way join in XQuery

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

Example 24-4. Grouping in XQuery

for $d in distinct-values(doc("catalog2.xml")//product/@dept)
return
  <result dept="{$d}"
          count="{count(doc("catalog2.xml")//product[@dept = $d])}"/>

Example 24-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;