Find objects in other list thanks to a key

Question

I need to do a specific XQuery Transformation, in an OSB flow.

Input has this form :

<OBJECT_1>
  <item>   // multiplicy : *
    <MONTH>
    <INFO_11/>
    <INFO_12/>
  </item>
</OBJECT_1>
<OBJECT_2>
  <item>   // multiplicy : *
    <INFO_21/>
    <INFO_22/>
    <MONTH/>
  </item>
</OBJECT_2>

The target output has this structure :

<object1>   // multiplicy : *
  <month>
  <info11/>
  <info12/>
  <object2>   // multiplicy : *
    <info21/>
    <info22/>
  </object2>
</object1>

For the moment my xquery looks like:

declare function xf:myTransformation($z_SOURCE1 as element(ns1:SOURCE))
    as element(ns0:targetService) {
        <ns0:targetService>
            <myFlow>
               {
                    for $item in $z_SOURCE1/ns1:OBJECT_1/ns1:item
                    return
                       <object1>
                            {
                              for $MONTH in $item/ns1:MONTH
                                return
                                    <month>{ data($MONTH) }</month>
                            }
                            {
                              for $INFO_11 in $item/ns1:INFO_11
                                return
                                    <info11>{ data($INFO_11) }</info11>
                            }
                            {
                              for $INFO_12 in $item/ns1:INFO_12
                                return
                                    <info12>{ data($INFO_12) }</info12>
                            }
                            {
                            for $item0 in $z_SOURCE1/ns1:OBJECT_2/ns1:item
                            return
                              {
                                for $INFO_21 in $item0/ns1:INFO_21
                                  return
                                    <info21>{ data($INFO_21) }</info21>
                              }
                              {
                                for $INFO_22 in $item0/ns1:INFO_22
                                  return
                                    <info21>{ data($INFO_22) }</info22>
                              }
                       </object1>
               }
            </myFlow>
        </ns0:targetService>
 };

As you can see, there is no "matching" between OBJECT_1 and OBJECT_2...

How can I "find" in OBJECT_2 list the objects that match the object1 month key (in SQL : OBJECT_1.MONTH=OBJECT_2.MONTH) ? While building the OBJECT_1, can I walk into the OBJECT_2 list to find the keys I need ?

I tried to change the `for $item0 in $z_SOURCE1/ns1:OBJECT_2/ns1:item` by `for $item0 in $z_SOURCE1/ns1:OBJECT_2/ns1:item[KEY_MAPPING_OBJECT_1=../key[1]]` without a big success.. — MTranchant, Jul 03 '13 at 14:13

MTranchant · Accepted Answer · 2013-07-05T15:15:03.363

Answer found from OTN forum:

Use the below xquery:-

xquery version "1.0" encoding "Cp1252";
(:: pragma  parameter="$anyType1" type="xs:anyType" ::)
(:: pragma  type="xs:anyType" ::)


declare namespace xf = "http://tempuri.org/OSB%20Project%201/XQ/Test2/";


declare function xf:Test2($anyType1 as element(*))
    as element(*) {
       let $input:= <STRUCTURE> 
   <OBJECT_1> 
      <item> 
         <MONTH>2013_M06</MONTH> 
         <INFO_11>ABC</INFO_11> 
         <INFO_12>2012-04-24</INFO_12> 
      </item> 
      <item> 
         <MONTH>2013_M05</MONTH> 
         <INFO_11>DEF</INFO_11> 
         <INFO_12>2012-04-24</INFO_12> 
      </item> 
   </OBJECT_1> 
   <OBJECT_2> 
      <item> 
         <MONTH>2013_M06</MONTH> 
         <INFO_21>11111</INFO_21> 
         <INFO_22>1</INFO_22> 
      </item> 
      <item> 
         <MONTH>2013_M06</MONTH> 
         <INFO_21>11111</INFO_21> 
         <INFO_22>2</INFO_22> 
      </item> 
      <item> 
         <MONTH>2013_M05</MONTH> 
         <INFO_21>22222</INFO_21> 
         <INFO_22>1</INFO_22> 
      </item> 
   </OBJECT_2> 
</STRUCTURE>
let $month := fn:distinct-values($input/OBJECT_1//MONTH/text())
let $out:=<targetStructure>{
  for $x in 1 to fn:count($month)
  return
  <object1>
  <month>{$month[$x]}</month>
  <info_11>{$input/OBJECT_1/item[./MONTH=$month[$x]]/INFO_11/text()}</info_11>
  <info_12>{$input/OBJECT_1/item[./MONTH=$month[$x]]/INFO_12/text()}</info_12>
  {for $y in $input/OBJECT_2/item
  where $y/MONTH/text()=$month[$x]
  return
  <object2>

  <info_21>{$y/INFO_21/text()}</info_21>
  <info_22>{$y/INFO_22/text()}</info_22>
  </object2>}
  </object1>
}
</targetStructure>


return $out


};


declare variable $anyType1 as element(*) external;


xf:Test2($anyType1)

Find objects in other list thanks to a key

1 Answers1