This discussion is archived
2 Replies Latest reply: Sep 12, 2011 2:38 AM by user299932 RSS

Help with complex XQuery tranformation

user299932 Newbie
Currently Being Moderated
Hi,

I posted this message in SOA category but I think it makes sense to post it here also.

I have to write a complex XQuery tranformation for storing a XML document in database using a database adapter in Oracle Service Bus (transformation is from source document schema to database adapter schema).

I am having problems to develop XQuery for following XML segment:

<dataElementsStruct>
<elementManagementStructData>
<elementReference>
<qualifier>OT</qualifier>
<number>9</number>
</elementReference>
<segmentName>DID</segmentName>
</elementManagementStructData>
<referenceForStructDataElement>
<reference>
<qualifier>PT</qualifier>
<number>1</number>
</reference>
<reference>
<qualifier>PT</qualifier>
<number>2</number>
</reference
<reference>
<qualifier>ST</qualifier>
<number>1</number>
</reference>
<reference>
<qualifier>ST</qualifier>
<number>2</number>
</reference>
</referenceForStructDataElement>
</dataElementsStruct>
<dataElementStruct>
...
</dataElementStruct>

Each dataElementsStruct element must me mapped to a table row with the particularity that for each combination of PT and ST qualifier values a new row must be also created. So, for the previous example, 4 rows should be created (PT1-ST1, PT1-ST2, PT2-ST1, PT2-ST2) for the element.

Using Eclipse XQuery Mapper tool shipped with OSB I wrote following code:

<ns1:pnrTDiduciCollection>
{
for $dataElementsStruct in $dataElementsMaster/ns0:dataElementsStruct
return
<ns1:PnrTDiduci>
<ns1:elerefQualifier>{ data($dataElementsStruct/ns0:elementManagementStructData/ns0:elementReference
</ns1:PnrTDiduci>
}
</ns1:pnrTDiduciCollection>

This creates a <ns1:PnrTDiduci> which represents a table row for each dataElementStruct element. But as I said, I need a <ns1:PnrTDiduci> element for each PT-ST combination been able to access also current PT and ST number values for each combination pair.

I wonder if this can be expressed with XQuery or we have to use a different approach to perform this.

Any help will be greatly appreciated.

Thank you in advance,

Daniel.
  • 1. Re: Help with complex XQuery tranformation
    tsuji Journeyer
    Currently Being Moderated
    You may try this. A cartesian product involves an implicit assumption on the number(s) be unique, within each PT and ST group, that probably be so without you mentioning it. If not, what need is not very clear. Maybe you can then develop from the below to accommodate that complication.
    <ns1:pnrTDiduciCollection>
    {
    for $dataElementsStruct in $dataElementsMaster/ns0:dataElementsStruct
    return
    <ns1:PnrTDiduci>
    {
    for $ref in $dataElementsStruct/ns0:referenceForStructDataElement
    return
    for $ptref in $ref/ns0:reference[ns0:qualifier='PT']
    for $stref in $ref/ns0:reference[ns0:qualifier='ST']
    return
    <ns1:elerefQualifier>
    {
    concat(
        $ptref/ns0:qualifier,
        $ptref/ns0:number,
        '-',
        $stref/ns0:qualifier,
        $stref/ns0:number               
    )
    }
    </ns1:elerefQualifier>
    }
    </ns1:PnrTDiduci>
    }
    </ns1:pnrTDiduciCollection>
  • 2. Re: Help with complex XQuery tranformation
    user299932 Newbie
    Currently Being Moderated
    Thank you very much! This code example helped a lot.

    Regards,

    Daniel.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points