[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