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

[Adam Retter]

I dont think you can do this in one pass. However, depending on the
implementation its impossible to know how many passed the processor
will actually make over the source to fulfil the query.

However, if we were to assume each FLWOR expression is a pass over the
source data, then I think the following implementation could be more
efficient. It really depends on how the implementation handles
descendant-or-self and ancestor selection.


let $basicTypes := /BasicType
<Result>
	<BasicTypes>
	{
		$basicTypes
	}
	</BasicTypes>
	<ProductTypes>
	{
		for $productType in $basicTypes//ProductType return
			<ProductType>
			{
				$productType/*,
				<BasicType ref-id="{$productType/ancestor::BasicType/@id}"/>
			}
			</ProductTypes>
	}
	</ProductTypes>
	<SalesItems>
	{
		for $salesItem $basicTypes//SalesItem return
			<SalesItem>
			{
				$salesItem/*,
				<ProductType ref-id="{$salesItem/ancestor::ProductType/@id}"/>
			}
			</SalesItem>
	}
	</SalesItems>
</Result>



On 10 August 2012 15:32, Robby Pelssers <Robby.Pelssers at nxp.com> wrote:
> 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
>
>
> _______________________________________________
> talk at x-query.com
> http://x-query.com/mailman/listinfo/talk



-- 
Adam Retter

skype: adam.retter
tweet: adamretter
http://www.adamretter.org.uk