XQuery statement that groups results

Question

Hi

I have an XML structure that I need to query using XQuery. As far as I have been able to find, this may not even be possible, but since I am so new to XQuery I thought I may ask.

I need to query the XML below and return something that looks like:

<product>
 <title>Acer Liquid</title>
 <image>ACER-LIQUID.jpg</image>
 <networks>
  <network>O2O</network>
  <network>VOD</network>
 </networks>
 <colours>
  <colour>Blue</colour>
  <colour>Black</colour>
 </colours>
</product>

So my query is essentially something like: Get all product details where there is a salespackage that belongs to "Pay Monthly" that points to it (via main_product), and attach the network property from each sales package that points to it. But group the results by the product.product_model field.

Source XML:

<root>
 <package>
  <title>package1</title>
  <categories><category group="Other" name="Pay Monthly"/></categories>
  <properties>
    <value key="main_product">PRODUCT#14649766#PART#ACERLIQUIDBLACK</value>
    <value key="network_code">O2O</value>
  </properties>
 </package>
 <package>
  <title>package2</title>
  <categories><category group="Other" name="Pay Monthly"/></categories>
  <properties>
    <value key="main_product">PRODUCT#14649700#PART#ACERLIQUIDBLUE</value>
    <value key="network_code">VOD</value>
  </properties>
 </package>
 <product>
  <title>Acer Liquid</title>
  <properties>
   <value key="product_code">PRODUCT#14649700#PART#ACERLIQUIDBLUE</value>
   <value key="product_model">Acer Liquid|ACER_LIQUID</value>
   <value key="product_image_url">ACER-LIQUID.jpg</value>
   <value key="colour">Blue</value>
  </properties>
 </product>
 <product>
  <title>Acer Liquid</title>
  <properties>
   <value key="product_code">PRODUCT#14649766#PART#ACERLIQUIDBLACK</value>
   <value key="product_model">Acer Liquid|ACER_LIQUID</value>
   <value key="product_image_url">ACER-LIQUID.jpg</value>
   <value key="colour">Black</value>
  </properties>
 </product>
</root>

Answer 1

Quite complex query for XQuery 1.0:

declare ordering unordered;

declare function local:values($items as node()*,
                              $property as xs:string) as xs:string* {
    distinct-values($items/properties/value[@key eq $property])
};

declare function local:filter($items as node()*, 
                              $property as xs:string,
                              $values as xs:string*) as node()* {
    $items[properties/value[@key eq $property] = $values]
};


let $packages := doc('source')/root/package[categories/category[@name eq 'Pay Monthly']]
let $products := local:filter(doc('source')/root/product, 
                              'product_code',
                              local:values($packages, 'main_product'))

for $model           in local:values($products, 'product_model')
let $model_products  := local:filter($products, 'product_model', $model)
let $model_packages  := local:filter($packages,
                                     'main_product',
                                     local:values($model_products, 'product_code'))

return
<product>
    {$model_products[1]/title}
    <image>{local:values($model_products[1], 'product_image_url')}</image>
    <networks>{
        for $net in local:values($model_packages, 'network_code')
        return <network>{$net}</network>
    }</networks>
    <colours>{
        for $cl in local:values($model_products, 'colour')
        return <colour>{$cl}</colour>
    }</colours>
</product>

Answer 2

This XQuery:

for $title in (/root/product/title)[index-of(/root/product/title,.)[1]]
let $ProductsProp := /root/product[title=$title]/properties/value
return 
<product>
    {$title}
    <image>{string(($ProductsProp[@key='product_image_url'])[1])}</image>
    <networks>{
        for $network in /root/package/properties
                                  [value[@key='main_product']
                                   = $ProductsProp[@key='product_code']]
                                  /value[@key='network_code']/string()
        return
        <network>{$network}</network>
    }</networks>
    <colours>{
        for $colour in $ProductsProp[@key='colour']/string()
        return
        <colour>{$colour}</colour>
    }</colours>
</product>

Output:

<product>
    <title>Acer Liquid</title>
    <image>ACER-LIQUID.jpg</image>
    <networks>
        <network>O2O</network>
        <network>VOD</network>
    </networks>
    <colours>
        <colour>Blue</colour>
        <colour>Black</colour>
    </colours>
</product>

Note: XPath 2.0 grouping expression $vSeq[index-of($vSeq,.)[1]]