[xquery-talk] how to optimaly denormalize 1-to-many relationships with XQuery

Robby Pelssers Robby.Pelssers at nxp.com
Fri Aug 10 07:32:00 PDT 2012


Hi all,

Suppose I have a collection of XML documents looking like this:

Basictype has 1 to many Product types.
Producttype has 1 to many Sales items.

Example snippet:
---------------------------------
<BasicType id="PH3330L">
  <Status>End of life</Status>
  ...
  <ProductTypes>
    <ProductType id="xxx">
       <Status>Deprecated</Status>
       ...
       <SalesItems>
         <SalesItem id="yyy">
           <Owner>abcde</Owner>
         </SalesItem>
       </SalesItems>
    </ProductType>
  </ProductTypes>
</BasicType>

Now I want to generate some data looking like this:

<Result>
  <BasicTypes>
   <BasicType id="PH3330L">
     <Status>End of life</Status> 
    </BasicType>
    ...  
  </BasicTypes>
  <ProductTypes>
    <ProductType id="xxx">
       <Status>Deprecated</Status>
       <BasicType ref-id="PH3330L"/>
    </ProductType>
    ...
  </ProductTypes>
  <SalesItems>
    <SalesItem id="yyy">
      <Owner>abcde</Owner>
      <ProductType ref-id="xxx"/>
    </SalesItem>
    ...
  </SalesItems>  
</Result>

-------------
I have written a query which returns just this but it iterates 
- three times over the basictypes 
- 2 times over the producttypes 
- 1 time over the salesitems

Is there a better way to get this accomplished in 1 iteration?


Pseudo-code:

let $basictypes := collection("basictypes")
return
 <Result>
  <BasicTypes> 
   {
     for $basictype in $basictypes
     ...do some stuff
     
   }
  </BasicTypes>
  <ProductTypes>
    {
       for $basictype in $basictypes
       for $producttype in $basictype/ProductTypes/ProductType
       ...do some stuff
    }
  </ProductTypes>
  <SalesItems>
    {
       for $basictype in $basictypes
       for $producttype in $basictype/ProductTypes/ProductType
       for $salesitem in $producttype/SalesItems/SalesItem
       ...do some stuff
    }
  </SalesItems>
 </Result>

Robby Pelssers




More information about the talk mailing list