Auto increment with xquery

Recently i got a requirement to do auto increment in xquery.I checked lot of links to implement it,
but somehow i didn't got proper information about it.So i want to take this opportunity to share my work around to implement Auto increment xquery.

Goal:
1.Auto Increment xquery udpate
2.how to put header and detail entries with distinct rows in same table.
3.how to use predicate

Auto Increment xquery udpate:

Sample xml file format:
<PO_Receipt_Confirmations>
    <Receipt_Confirmation>
        <Confirmation_Header>
            <warehouse>OML</warehouse>
            <customer_code>XP</customer_code>
            <Logitech_PO_number>71201516</Logitech_PO_number>
            <receipt_number>12341</receipt_number>
            <doc_type>RD</doc_type>
           </Confirmation_Header>
        <Confirmation_Detail>
            <Logitech_PO_line_number>6</Logitech_PO_line_number>
            <product_code>920-004967</product_code>
            <product_description>GAMING</product_description>
            <actual_receipt_qt>3</actual_receipt_qt>
            <unit_of_measure>EA</unit_of_measure>
            <subinventory>UZN</subinventory>
            <waybill_number/>
        </Confirmation_Detail>
       <Confirmation_Detail>
            <Logitech_PO_line_number>6</Logitech_PO_line_number>
            <product_code>920-004967</product_code>
            <product_description>GAMING</product_description>
            <actual_receipt_qt>12</actual_receipt_qt>
            <unit_of_measure>EA</unit_of_measure>
            <subinventory>UZN</subinventory>
            <waybill_number/>
        </Confirmation_Detail>
       <Confirmation_Detail>
            <Logitech_PO_line_number>9</Logitech_PO_line_number>
            <product_code>981-000536</product_code>
            <product_description>G430</product_description>
            <actual_receipt_qt>7</actual_receipt_qt>
            <unit_of_measure>XP</unit_of_measure>
            <subinventory>UZN</subinventory>
            <waybill_number/>
        </Confirmation_Detail>
    </Receipt_Confirmation>
   <Receipt_Confirmation>
        <Confirmation_Header>
            <warehouse>OML</warehouse>
            <customer_code>NZ</customer_code>
            <Logitech_PO_number>71201516</Logitech_PO_number>
            <receipt_number>1000536687</receipt_number>
            </Confirmation_Header>
        <Confirmation_Detail>
            <Logitech_PO_line_number>6</Logitech_PO_line_number>
            <product_code>45234</product_code>
            <product_description>G510S</product_description>
            <actual_receipt_qt>3</actual_receipt_qt>
            <unit_of_measure>EA</unit_of_measure>
            <subinventory>UZN</subinventory>
            <waybill_number/>
        </Confirmation_Detail>
        <Confirmation_Detail>
            <Logitech_PO_line_number>6</Logitech_PO_line_number>
            <product_code>45234</product_code>
            <product_description>G510S</product_description>
            <actual_receipt_qt>3</actual_receipt_qt>
            <unit_of_measure>EA</unit_of_measure>
            <subinventory>UZN</subinventory>
            <waybill_number/>
        </Confirmation_Detail>
        <Confirmation_Detail>
            <Logitech_PO_line_number>9</Logitech_PO_line_number>
            <product_code>981-000536</product_code>
            <product_description>G430</product_description>
            <actual_receipt_qt>7</actual_receipt_qt>
            <unit_of_measure>EA</unit_of_measure>
            <subinventory>UZN</subinventory>
            <waybill_number/>
        </Confirmation_Detail>
    </Receipt_Confirmation>
</PO_Receipt_Confirmations>

In this above xml file we have two receipt_Confirmation with one header and multiple details distinctly.When inserting into table through xquery mapping,We have to capture position of the first receipt header and details position and second receipt as well.

for ex:
We have two receipts like below
source xml:
<PO>
<Receipt>                position:
<header></header>  1
<detail></detail>      2
<detail></detail>      3
</Receipt>
<Receipt>
<header></header>   4
<detail></detail>      5
<detail></detail>      6
</Receipt>

<PO>

Requirement need to be achieved:

<addPoReceiptConfirmation>
    <poReceiptConfirmationRequest>
        <recordHeaderId>1</recordHeaderId>
        <recordType>H</recordType>
        <seqNum>1</seqNum>
    </poReceiptConfirmationRequest>
    <poReceiptConfirmationRequest>
        <recordHeaderId>2</recordHeaderId>
        <recordType>L</recordType>
        <seqNum>2</seqNum>
    </poReceiptConfirmationRequest>
    <poReceiptConfirmationRequest>
        <recordHeaderId>3</recordHeaderId>
        <recordType>L</recordType>
        <seqNum>3</seqNum>
    </poReceiptConfirmationRequest>
    <poReceiptConfirmationRequest>
        <recordHeaderId>4</recordHeaderId>
        <recordType>H</recordType>
        <seqNum>4</seqNum>
    </poReceiptConfirmationRequest>
    <poReceiptConfirmationRequest>
        <recordHeaderId>5</recordHeaderId>
        <recordType>L</recordType>
        <seqNum>5</seqNum>
    </poReceiptConfirmationRequest>
    <poReceiptConfirmationRequest>
        <recordHeaderId>6/recordHeaderId>
            <recordType>L</recordType>
            <seqNum>6</seqNum>
        </poReceiptConfirmationRequest>
    </addPoReceiptConfirmation>

Below xquery will help for achieving the auto increment number:
declare function xf:POReceiptConfim_3PL_Inflow($anyXML as element(*))
    as element(ns0:addPoReceiptConfirmation) {
    return
  <ns0:addPoReceiptConfirmation>
<poReceiptConfirmationRequest>
{
for $Receipt_Confirmation at $RcptPOS in 
$anyXML/Receipt_Confirmation,
$Confirmation_Header at $HdrPos in $Receipt_Confirmation/Confirmation_Header
let $HdrPOSIT:=xs:int($RcptPOS)-1
let $hdrSum:=
count($anyXML/Receipt_Confirmation[position()>=1 and position()<=$HdrPOSIT]/Confirmation_Detail)
let $diff := xs:int($hdrSum) -  xs:int(sum(for $item in 1 to $HdrPOSIT return count(distinct-values($anyXML/Receipt_Confirmation[$item]/Confirmation_Detail/product_code))))
let $SUM := $hdrSum - $diff + xs:int($RcptPOS) 
return
(
       let $collection:=(
 <Collection> <poReceiptConfirmations> {if($RcptPOS>1) then <recordHeaderId>{($SUM)}</recordHeaderId> else <recordHeaderId>{$HdrPos}</recordHeaderId> } <recordType>H</recordType> {if($RcptPOS>1) then <seqNum>{($SUM)}</seqNum> else <seqNum>{$HdrPos}</seqNum> } </poReceiptConfirmations> { let $ProductCode:=distinct-values($Receipt_Confirmation/Confirmation_Detail/product_code) for $product_code at $linePOS in $ProductCode let $actual_receipt_qt_count:=fn:sum($Receipt_Confirmation/Confirmation_Detail[product_code=$product_code]/actual_receipt_qt)[1] let $unit_of_measure:=($Receipt_Confirmation/Confirmation_Detail[product_code=$product_code]/unit_of_measure)[1] let $Logitech_PO_line_number:=($Receipt_Confirmation/Confirmation_Detail[product_code=$product_code]/Logitech_PO_line_number)[1] return <poReceiptConfirmations> {if(data($Confirmation_Header/doc_type)='PE') then () else (<itemNum>{data($product_code)}</itemNum> ) } <quantity>{xs:integer($actual_receipt_qt_count)}</quantity> {if($RcptPOS>1) then <recordHeaderId>{$SUM+$linePOS}</recordHeaderId> else <recordHeaderId>{$HdrPos+$linePOS}</recordHeaderId> } <recordType>L</recordType> {if($RcptPOS>1) then <seqNum>{$SUM+$linePOS}</seqNum> else <seqNum>{$HdrPos+$linePOS}</seqNum> } </poReceiptConfirmations> } </Collection>
)
return 
$collection/*
)
}
 </poReceiptConfirmationRequest>
</ns0:addPoReceiptConfirmation>
};

let me explain you about by taking small piece of above xquery:
for $Receipt_Confirmation at $RcptPOS in 
$anyXML/Receipt_Confirmation,
$Confirmation_Header at $HdrPos in $Receipt_Confirmation/Confirmation_Header
let $HdrPOSIT:=xs:int($RcptPOS)-1
let $hdrSum:=
count($anyXML/Receipt_Confirmation[position()>=1 and position()<=$HdrPOSIT]/Confirmation_Detail)
let $diff := xs:int($hdrSum) -  xs:int(sum(for $item in 1 to $HdrPOSIT return count(distinct-values($anyXML/Receipt_Confirmation[$item]/Confirmation_Detail/product_code))))
let $SUM := $hdrSum - $diff + xs:int($RcptPOS) 

As most of you know about 'at' keyword why we have used in this xquery to check the position of the loop like above we have used it.In xquery we can get the position upto in the loop boundary

<PO>
<Receipt>         
<header></header> 
<detail></detail> 
<detail></detail> 
</Receipt>
<Receipt>
<header></header> 
<detail></detail> 
<detail></detail> 
</Receipt>
<PO>
This xml file is having two receipts,if you loop on receipt(<Receipt>) element.We can get only first recipt position like 1,2,3 for the second receipts we will get again 1,2,3.Since we are looping on Receipt we don't have a control to get the last position of the previous receipt.So to achive this logic,if you could know the above receipt last detail position then our task will be completed.In this case
(position()) function,We can use to get the previous receipt last detail position.

RcptPOS is telling about loop position of the Receipt_Confirmation,HdrPOSIT is helpful for calculating the previous receipt,$hdrSum is helpful for calculating count of the previous loop detail count.

explanation:
xs:int(sum(for $item in 1 to $HdrPOSIT return count(distinct-values($anyXML/Receipt_Confirmation[$item]/Confirmation_Detail/product_code))))
above statement will helpful to check the previous distinct product-code detail position count by summation till  HdrPOSIT satisfies.

Dynamic Xquery Transformations in OSB

Dynamic transformations allow you to define a proxy service which can handle new transformations being added at a later date.Let's look at how we might implement a typical scenario where a proxy service accepts multiple versions of a message and applies an appropriate transformation based on the version of the message.

Goal:
In this POC Iam having 3 XQ Transformations for calling this i made one RuleSet which will helpfull for calling my Xquery transformations based open my input.My task is to Call Dynamically these 3 Xquery transformations based upon the Operation input given from input proxy data.

First create a new Oracle Service Bus project and name it as DynamicXqueryPOC (File>New>Oracle Service Bus Project, enter a name of DynamicXqueryPOC and click Finish).

In the main editor pane, select the General tab for this service and make sure it is defined as a Messaging Service.Now move to the Messaging tab,set the Request and Response Type to XML.Go the  Message flow tab and take the pipeline-pair and put the stage inside to create a logic.Drag a Assign activity under the Stage and give $body/* into expression and assign to Request variable.

For passing the data to the below xquery take two assign activity to capture element1 and element2 and pass this data to the below xquery.

Xquery will accept the 2 input strings
xquery version "1.0" encoding "Cp1252";
declare namespace xf = "http://tempuri.org/DynamicPOC/Resources/Version/";
declare function xf:Version($string1 as xs:string,
    $string2 as xs:string)
    as xs:string {
        concat($string1,$string2,'VersionCalled')
};

declare variable $string1 as xs:string external;
declare variable $string2 as xs:string external;

xf:Version($string1,
    $string2)

Create 3 Xquery's with simple concat operations like below and name the xquery transformations as Version.xq,Version1.xq and Version2.xq.

Create RoutingRule xquery transformation for mention the rules and getServicesXQ is for calling the dynamic xquery's Version,Version1 and Version2 based upon the Operation
1.RoutingRule.xq
<Rules>
  <Operations>
    <Operation Name="Operation1">
      <RoutingRules>
        <RoutingRule>
          <Transformation>
            <XQ Name="Case1_XQ"/>
          </Transformation>
        </RoutingRule>
      </RoutingRules>
    </Operation>
    <Operation Name="Operation2">
      <RoutingRules>
        <RoutingRule>
          <Transformation>
            <XQ Name="Case2_XQ"/>
          </Transformation>
        </RoutingRule>
      </RoutingRules>
    </Operation>
    <Operation Name="Operation3">
      <RoutingRules>
        <RoutingRule>
          <Transformation>
            <XQ Name="Case3_XQ"/>
          </Transformation>
        </RoutingRule>
      </RoutingRules>
    </Operation>
  </Operations>
  <RoutingServices>
    <RoutingXQ Name="Case1_XQ">
      <Address>DynamicPOC/Resources/Version</Address>
    </RoutingXQ>
    <RoutingXQ Name="Case2_XQ">
      <Address>DynamicPOC/Resources/Version1</Address>
    </RoutingXQ>
    <RoutingXQ Name="Case3_XQ">
      <Address>DynamicPOC/Resources/Version2</Address>
    </RoutingXQ>
  </RoutingServices>
</Rules>
Take this above Xquery in assign activity and map to the routingRules variable.

getServicesXQ.xq
xquery version "1.0" encoding "Cp1252";
(:: pragma  parameter="$Rules" type="xs:anyType" ::)
(:: pragma  parameter="$Operation" type="xs:anyType" ::)
(:: pragma  type="xs:string" ::)
declare namespace xf = "http://tempuri.org/Examples/GetRoutingXQ";
declare function xf:getServicesXQ($Rules as element(*), $Operation as xs:string)
as xs:string {
let $Result := for $Rule in $Rules//Operations/Operation[@Name= $Operation]/RoutingRules/*
let $TransXQOperation:= $Rule/Transformation/XQ/@Name
for $RoutingAddress in $Rules//RoutingServices/RoutingXQ[@Name = $TransXQOperation]/Address
return $RoutingAddress
return data($Result)
};
declare variable $Rules as element(*) external;
declare variable $Operation as xs:string external;
xf:getServicesXQ($Rules,$Operation)

Take this into assign activity and pass the input's routingRules and Operation variable and name it as dynaXQ like below.

This query will helpful for calling the xquery transformations.Finally take one more assign activity to call the xquery dynamically like below.

In the above assign activity i have passed the $dynaXQ in the Expression and browsed Version.xq .Once you browsed the Version xqery you will able to see the bind variables.

Use this below xml data when testing your proxy service.
<Route>
<element1>hi</element1>
<element2>My</element2>
<Operation>Operation1</Operation>
</Route>

Please find the jar for the sample code below.
https://drive.google.com/open?id=0B8cpEjk4gP0GM1l0dENtOXJNQUU&authuser=0