2 Replies Latest reply: Sep 12, 2011 4:38 AM by Daniel C RSS

    Help with complex XQuery tranformation

    Daniel C
      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
          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
            Daniel C
            Thank you very much! This code example helped a lot.

            Regards,

            Daniel.