XQuery Examples: Advanced Queries

Example 9-1. Attempting to use a counter variable

Query

let $count := 0
for $prod in doc("catalog.xml")//product[@dept = ("ACC", "WMN")]
let $count := $count + 1
return <p>{$count}. {data($prod/name)}</p>

Results

<p>1. Fleece Pullover</p>
<p>1. Floppy Sun Hat</p>
<p>1. Deluxe Travel Bag</p>

Example 9-2. Attempting to use the position function

for $prod in doc("catalog.xml")//product[@dept = ("ACC", "WMN")]
return <p>{$prod/position()}. {data($prod/name)}</p>

Example 9-3. Using a positional variable in a for clause

Query

for $prod at $count in doc("catalog.xml")//product[@dept = ("ACC", "WMN")]
return <p>{$count}. {data($prod/name)}</p>

Results

<p>1. Fleece Pullover</p>
<p>2. Floppy Sun Hat</p>
<p>3. Deluxe Travel Bag</p>

Example 9-4. Attempting to use a positional variable with a where clause

Query

for $prod at $count in doc("catalog.xml")//product
where $prod/@dept = ("ACC", "MEN")
order by $prod/name
return <p>{$count}. {data($prod/name)}</p>

Results

<p>4. Cotton Dress Shirt</p>
<p>3. Deluxe Travel Bag</p>
<p>2. Floppy Sun Hat</p>

Example 9-5. Embedding the where clause

Query

let $sortedProds := for $prod in doc("catalog.xml")//product
                    where $prod/@dept = "ACC" or $prod/@dept = "MEN"
                    order by $prod/name
                    return $prod
for $sortedProd at $count in $sortedProds
return <p>{$count}. {data($sortedProd/name)}</p>

Results

<p>1. Cotton Dress Shirt</p>
<p>2. Deluxe Travel Bag</p>
<p>3. Floppy Sun Hat</p>

Example 9-6. Using a count clause

xquery version "3.0";
for $prod in doc("catalog.xml")//product
where $prod/@dept = ("ACC", "MEN")
order by $prod/name
count $count
return <p>{$count}. {data($prod/name)}</p>

Example 9-7. Using a count clause for ranking

Query

xquery version "3.0";
for $prod in doc("prices.xml")//prod
order by $prod/price/number(.) descending
count $count
where $count <= 2
return <p>{$count}. {data($prod/price)}</p>

Results

<p>1. 69.99</p>
<p>2. 39.99</p>

Example 9-8. Using the count clause with groups

Query

xquery version "3.0";
for $item in doc("order.xml")//item
group by $d := $item/@dept
count $count
return <p>{concat("Group ", $count, ": ", $d)}</p>

Results

<p>Group 1: ACC</p>
<p>Group 2: MEN</p>
<p>Group 3: WMN</p>

Example 9-9. Testing for the last item

Query

<p>{ let $prods := doc("catalog.xml")//product
     let $numProds := count($prods)
     for $prod at $count in $prods
     return if ($count = $numProds)
            then concat($prod/name, ".")
            else concat($prod/name, ",")
}</p>

Results

<p>Fleece Pullover, Floppy Sun Hat, Deluxe Travel Bag, Cotton Dress Shirt.</p>

Example 9-10. Testing for the last item using the is operator

<p>{ let $prods := doc("catalog.xml")//product
     for $prod in $prods
     return if ($prod is $prods[last()])
            then concat($prod/name, ".")
            else concat($prod/name, ", ")
}</p>

Example 9-11. Using the window clause

Query

xquery version "3.0";
for tumbling window $w in (1, 4, 3, 12, 5, 13, 8)
  start $s when $s mod 2 = 0
return <window>{$w}</window>

Results

<window>4 3</window>
<window>12 5 13</window>
<window>8</window>

Example 9-12. Using the end condition

Query

xquery version "3.0";
let $props := <properties>
                <propname>x</propname>
                <value>xval</value>
                <alt-value>xval2</alt-value>
                <propname>y</propname>
                <value>yval</value>
                <alt-value>yval2</alt-value>
              </properties>
for tumbling window $w in $props/*
  start $s when name($s) = 'propname'
  end $e when name($e) = 'value'
return <property>{$w}</property>

Results

<property>
   <propname>x</propname>
   <value>xval</value>
</property>
<property>
   <propname>y</propname>
   <value>yval</value>
</property>

Example 9-13. Using the start and end positions

Query

xquery version "3.0";
for tumbling window $w in (1, 4, 3, 12, 5, 13, 8)
  start at $s-pos when true()
  end at $e-pos when $e-pos - $s-pos = 2
return <window>{$w}</window>

Results

<window>1 4 3</window>
<window>12 5 13</window>
<window>8</window>

Example 9-14. Using the only keyword

Query

xquery version "3.0";
for tumbling window $w in (1, 4, 3, 12, 5, 13, 8)
  start at $s-pos when true()
  only end at $e-pos when $e-pos - $s-pos = 2
return <window>{$w}</window>

Results

<window>1 4 3</window>
<window>12 5 13</window>

Example 9-15. Using the previous keyword

Query

xquery version "3.0";
for tumbling window $w in doc("order.xml")//item
  start $s previous $s-prev when $s/@dept != $s-prev/@dept
return (<p>Department {data($s/@dept)}</p>, $w)

Results

<p>Department ACC</p>
<item dept="ACC" num="563" quantity="1"/>
<item dept="ACC" num="443" quantity="2"/>
<p>Department MEN</p>
<item dept="MEN" num="784" quantity="1" color="white"/>
<item dept="MEN" num="784" quantity="1" color="gray"/>
<p>Department WMN</p>
<item dept="WMN" num="557" quantity="1" color="black"/>

Example 9-16. Using the next keyword

Query

xquery version "3.0";
for tumbling window $w in doc("order.xml")//item
  start $s when true()
  end $e next $e-next when string($e/@dept) != string($e-next/@dept)
return (<p>Department {data($s/@dept)}</p>, $w)

Results

<p>Department WMN</p>
<item dept="WMN" num="557" quantity="1" color="navy"/>
<p>Department ACC</p>
<item dept="ACC" num="563" quantity="1"/>
<item dept="ACC" num="443" quantity="2"/>
<p>Department MEN</p>
<item dept="MEN" num="784" quantity="1" color="white"/>
<item dept="MEN" num="784" quantity="1" color="gray"/>
<p>Department WMN</p>
<item dept="WMN" num="557" quantity="1" color="black"/>

Example 9-17. Sliding windows

Query

xquery version "3.0";
for sliding window $w in (1, 4, 3, 12, 5, 13, 8)
  start at $s-pos when true()
  only end at $e-pos when $e-pos - $s-pos = 2
return <window>{$w}</window>

Results

<window>1 4 3</window>
<window>4 3 12</window>
<window>3 12 5</window>
<window>12 5 13</window>
<window>5 13 8</window>

Example 9-18. Useful function: functx:add-attributes

declare namespace functx = "http://www.functx.com";
declare function functx:add-attributes
  ( $elements as element()*,
    $attrNames as xs:QName*,
    $attrValues as xs:anyAtomicType* )  as element()* {
       
   for $element in $elements
   return element { node-name($element)}
                  { for $attrName at $seq in $attrNames
                    return if ($element/@*[node-name(.) = $attrName])
                           then ()
                           else attribute {$attrName}
                                          {$attrValues[$seq]},
                    $element/@*,
                    $element/node() }
};

Example 9-19. Useful function: functx:remove-attributes

declare namespace functx = "http://www.functx.com";
declare function functx:remove-attributes
  ($elements as element()*, $names as xs:string*) as element()* {

   for $element in $elements
   return element
     {node-name($element)}
     {$element/@*[not(name() = $names)],
      $element/node() }
};

Example 9-20. Useful function: functx:remove-attributes-deep

declare namespace functx = "http://www.functx.com";
declare function functx:remove-attributes-deep
  ($nodes as node()*, $names as xs:string* )  as node()* {

   for $node in $nodes
   return if ($node instance of element())
          then  element { node-name($node)}
                { $node/@*[not(name() = $names)],
                  functx:remove-attributes-deep($node/node(), $names)}
          else if ($node instance of document-node())
          then functx:remove-attributes-deep($node/node(), $names)
          else $node
};

Example 9-21. Useful function: functx:remove-elements-deep

declare namespace functx = "http://www.functx.com";
declare function functx:remove-elements-deep
 ($nodes as node()*, $names as xs:string*)  as node()* {

   for $node in $nodes
   return
     if ($node instance of element())
     then if (name($node)=$names)
          then ()
          else element { node-name($node)}
                { $node/@*,
                  functx:remove-elements-deep($node/node(), $names)}
     else if ($node instance of document-node())
     then functx:remove-elements-deep($node/node(), $names)
     else $node
};

Example 9-22. Useful function: functx:remove-elements-not-contents

declare namespace functx = "http://www.functx.com";
declare function functx:remove-elements-not-contents
  ($nodes as node()*, $names as xs:string*) as node()* {

   for $node in $nodes
   return
    if ($node instance of element())
    then if (name($node) = $names)
         then functx:remove-elements-not-contents($node/node(), $names)
         else element {node-name($node)}
              {$node/@*,
              functx:remove-elements-not-contents($node/node(), $names)}
    else if ($node instance of document-node())
    then functx:remove-elements-not-contents($node/node(), $names)
    else $node
};

Example 9-23. Useful function: functx:change-element-names-deep

declare namespace functx = "http://www.functx.com";
declare function functx:change-element-names-deep
  ($nodes as node()*, $oldNames as xs:QName*,
   $newNames as xs:QName*) as node()* {

  if (count($oldNames) != count($newNames))
  then error(xs:QName("Different_Number_Of_Names"))
  else for $node in $nodes
       return if ($node instance of element())
              then let $newName :=
                     if (node-name($node) = $oldNames)
                     then $newNames[index-of($oldNames, node-name($node))]
                     else node-name($node)
                   return element {$newName}
                     {$node/@*,
                      functx:change-element-names-deep($node/node(),
                                              $oldNames, $newNames)}
              else $node
};

Example 9-24. Using the functx:change-element-names-deep function

Query

declare namespace functx = "http://www.functx.com";
let $order := doc("order.xml")/order
let $oldNames := (xs:QName("order"), xs:QName("item"))
let $newNames := (xs:QName("purchaseOrder"), xs:QName("purchasedItem"))
return functx:change-element-names-deep($order, $oldNames, $newNames)

Results

<purchaseOrder num="00299432" date="2015-09-15" cust="0221A">
  <purchasedItem dept="WMN" num="557" quantity="1" color="navy"/>
  <purchasedItem dept="ACC" num="563" quantity="1"/>
  <purchasedItem dept="ACC" num="443" quantity="2"/>
  <purchasedItem dept="MEN" num="784" quantity="1" color="white"/>
  <purchasedItem dept="MEN" num="784" quantity="1" color="gray"/>
  <purchasedItem dept="WMN" num="557" quantity="1" color="black"/>
</purchaseOrder>

Example 9-25. Converting values without a lookup table

Query

let $cat := doc("catalog.xml")/catalog
for $dept in distinct-values($cat/product/@dept)
return  <li>Department: {if ($dept = "ACC")
                        then "Accessories"
                        else if ($dept = "MEN")
                             then "Menswear"
                             else if ($dept = "WMN")
                                  then "Womens"
                                  else ()
               }  ({$dept})</li>

Results

<li>Department: Womens  (WMN)</li>
<li>Department: Accessories  (ACC)</li>
<li>Department: Menswear  (MEN)</li>

Example 9-26. Converting values with a lookup table

let $deptNames := <deptNames>
                    <dept code="ACC" name="Accessories"/>
                    <dept code="MEN" name="Menswear"/>
                    <dept code="WMN" name="Womens"/>
                  </deptNames>
let $cat := doc("catalog.xml")/catalog
for $dept in distinct-values($cat/product/@dept)
return <li>Department: {data($deptNames/dept[@code = $dept]/@name)
                  } ({$dept})</li>

Example 9-27. Reducing complexity

Query

let $tempResults:= 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}"
                                color="{$item/@color}"
                                quant="{$item/@quantity}"/>
return <table>
        <tr>
          <th>#</th><th>Name</th><th>Color</th><th>Quan</th>
        </tr>
        {for $lineItem in $tempResults
         return <tr>
                  <td>{data($lineItem/@num)}</td>
                  <td>{data($lineItem/@name)}</td>
                  <td>{data($lineItem/@color)}</td>
                  <td>{data($lineItem/@quant)}</td>
                </tr>
        }
     </table>

Value of $tempResults

<item num="557" color="navy" name="Fleece Pullover" quant="1"/>
<item num="563" color="" name="Floppy Sun Hat" quant="1"/>
<item num="443" color="" name="Deluxe Travel Bag" quant="2"/>
<item num="784" color="white" name="Cotton Dress Shirt" quant="1"/>
<item num="784" color="gray" name="Cotton Dress Shirt" quant="1"/>
<item num="557" color="black" name="Fleece Pullover" quant="1"/>

Partial Results

<table>
   <tr>
      <th>#</th><th>Name</th><th>Color</th><th>Quan</th>
   </tr>

   <tr>
      <td>557</td>
      <td>Fleece Pullover</td>
      <td>navy</td>
      <td>1</td>
   </tr>
   <!-- ... -->
</table>

Chapter 9: Advanced Queries