Tuesday 19 January 2016

Usefull Xquery's and XSLT's

Format Date

---------------------------------------------------------
<FormatRoot>
        <FormatDate>{ xs:date(fn:replace(fn:replace($Input/day,"(-|/| )",""),'(\d{2})(\d{2})(\d{4})', '$3-$1-$2')) } </FormatDate>
        <FormatInt>{fn-bea:decimal-truncate($Input/cnt)}</FormatInt>
       </FormatRoot>
---------------------------------------------------------
XSLT
xp20:format-dateTime(xp20:current-dateTime(),'[Y0001][M01][D01][h01][m01][s01][f01]')

OSB
fn-bea:dateTime-to-string-with-format("MM/dd/yyyy", xs:dateTime(data($clientApplication/ns0:planYearEffectiveDate))

Time in AM/PM
fn-bea:dateTime-to-string-with-format("MMM dd, YYYY hh:mm:ss a",fn:current-dateTime())
fn-bea:dateTime-from-string-with-format("MMM dd, YYYY hh:mm:ss a", "Dec 22, 2015 04:07:28 AM")


****************************************************************************************

Format Number in OSB

Xquery code to format any phone number to us format.
<ns0:phoneNbr>{fn:replace(fn:replace($phoneNbr,"\(|-|\)| ",""),'(\d{3})(\d{3})(\d{4})', '($1) $2-$3')}</ns0:phoneNbr>

****************************************************************************************

fn:string-join in OSB

Create a string from the node set in Assign using OSB

fn:string-join(
for $cmsep in $varDummiMTVResp1/gro:P_DIV_NUMBER_ITEM
return
data($cmsep/text()),',')

Create node set from delimited string
<BL4>{
for $id in fn:tokenize($FeinVariable/FEINList[1]/BL4,',')
return
<BL4List>{ $id }</BL4List>
    } </BL4>

**************************************************************************************** 

 local-name in OSB


<http:query-parameters>
{
 for $param in $body/request/*
 return
 <http:parameter name = "{local-name($param)}" value = "{data($param)}"/>
}
</http:query-parameters>

****************************************************************************************

Grouping in XQuery

*************************************************************************************
declare function xf:GroupTransform($groupSetup1 as element(ns1:GroupSetup))
    as element(ns0:GroupSetup) {
        <ns0:GroupSetup>
            <ns0:EmployerGroups>
                {
                    for $g in fn:distinct-values($groupSetup1/ns1:PartnerGroupInfo/ns1:Partner_Group_Number)
                    return
                        <ns0:EmployerGroupInformation>
                            <ns0:Employer>
                                <ns0:Name>{ $g }</ns0:Name>
                            </ns0:Employer>
                            <ns0:Plans>
                                {
                                for $grpset in $groupSetup1/ns1:PartnerGroupInfo
                                where $grpset[ns1:Partner_Group_Number eq $g]/ns1:Plan_ID/text() != ''
                                return
                                <ns0:plan>{ data($grpset[ns1:Partner_Group_Number eq $g]/ns1:Plan_ID/text()) }</ns0:plan>
                                }
                            </ns0:Plans>
                        </ns0:EmployerGroupInformation>
                }
            </ns0:EmployerGroups>
        </ns0:GroupSetup>
};
*************************************************************************************

 fn-bea:execute-sql


fn-bea:execute-sql('jdbc/soademoDB', xs:QName('ConfigData'),    'select ID,KEY,VALUE from demo_config where key = ?',
     $varKeyName)

fn-bea:execute-sql('jdbc/soademoDB',  xs:QName('ConfigData'),   $varKeyMulValues)

fn:concat("select ID,KEY,VALUE from demo_config where key like '",$body/ret:ConfigMultipleDataRequest/ret:KeyValueSearch/text(),"%'")


Adding Namespace

*************************************************************************************
xquery version "1.0" encoding "Cp1252";
(:: pragma parameter="$noNamespaceXML" type="xs:anyType" ::)
(:: pragma parameter="$namespaceURI" type="xs:string" ::)
(:: pragma type="xs:anyType" ::)
declare namespace xf = "<a href="http://tempuri.org/Resources/XQueries/addNamespace/">http://tempuri.org/Resources/XQueries/addNamespace/</a>";
declare function xf:addNamespaceToXML($noNamespaceXML as element(*),$namespaceURI as xs:string) as element(*)
{
element {fn:expanded-QName($namespaceURI,fn:local-name($noNamespaceXML))}
{
$noNamespaceXML/@*,
for $node in $noNamespaceXML/node()
return
if (exists($node/node())) then xf:addNamespaceToXML($node,$namespaceURI)
else if ($node instance of element()) then element {fn:expanded-QName($namespaceURI,fn:local-name($node))}{$node/@*}
else $node }
};
declare variable $noNamespaceXML as element(*) external;
declare variable $namespaceURI as xs:string external ; 
xf:addNamespaceToXML($noNamespaceXML, $namespaceURI)
*************************************************************************************

ora:setCompositeInstanceTitle($varTitle)


****************************************************************************************

oraext:query-database in xslt

oraext:query-database(concat("SELECT case_master_id FROM dbo.case_header WHERE case_header_number='",bpws:getVariableData('inputVariable','payload','/client:process/client:input'),"'"),false(),false(),"jdbc/SynergyDB")

concat("select isnull((select supplier_asn_nn from Supplier_Master where supplier_number='",$HeaderElement/ns1:HeaderElement/ns1:supplier_number,"'),0) next_num")

Update Sales_Order_Header Set Order_Status=90 Where Order_number=#Order_number and  0=(Select count(*) From Sales_Order_Header H,Sales_Order_Detail D Where H.Order_Number=D.Order_Number And  D.Order_Number=#Order_number_inner And D.Orderlinestatus Not In (330,900) ) 

concat(substring("00000",1,5-string-length(/client:process/client:input2)),/client:process/client:input2)

****************************************************************************************

oraext:max-value-among-nodeset in xslt

oraext:max-value-among-nodeset(oraext:query-database(("select delivery_number from dbo.home_delivery_header"),false(),false(),"jdbc/SynergyDB"))

****************************************************************************************

Grouping in XSLT

****************************************************************************************
  <xsl:template match="/">
    <ns1:POHeader>
    <xsl:variable name="distvalues"
             select="distinct-values(/ns0:PurchaseOrderDBOutputCollection/ns0:PurchaseOrderDBOutput/ns0:supplier_number)"/>
      <xsl:for-each select="$distvalues">  
          <ns1:PODetailCollection>
            <ns1:SupplierNumber>
              <xsl:value-of select="."/>
            </ns1:SupplierNumber>
            <xsl:variable name="supplierno" select="."/>
         <xsl:for-each select="/ns0:PurchaseOrderDBOutputCollection/ns0:PurchaseOrderDBOutput">
         <xsl:if test="ns0:supplier_number=$supplierno">
            <ns1:poDetailCollection>
              <ns1:PONumber>
                <xsl:value-of select="ns0:purchase_order_number"/>
              </ns1:PONumber>
              <ns1:asn>
                <xsl:value-of select="ns0:asn"/>
              </ns1:asn>
              <ns1:lpn>
                <xsl:value-of select="ns0:lpn"/>
              </ns1:lpn>
              <ns1:purchase_order_line_number>
                <xsl:value-of select="ns0:purchase_order_line_number"/>
              </ns1:purchase_order_line_number>
              <ns1:po_status>
                <xsl:value-of select="ns0:po_status"/>
              </ns1:po_status>
              <ns1:Sku>
                <xsl:value-of select="ns0:Sku"/>
              </ns1:Sku>
              <ns1:order_number>
                <xsl:value-of select="ns0:order_number"/>
              </ns1:order_number>
              <ns1:order_line_number>
                <xsl:value-of select="ns0:order_line_number"/>
              </ns1:order_line_number>
              <ns1:fulfilment_type>
                 <xsl:value-of select="ns0:fulfilment_type"/>
              </ns1:fulfilment_type>
            </ns1:poDetailCollection>
            </xsl:if>
         </xsl:for-each>
          </ns1:PODetailCollection>
        </xsl:for-each>
  
    </ns1:POHeader>
  </xsl:template>
</xsl:stylesheet>
OR

 <?xml version="1.0"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
   <MailPayload>
<xsl:for-each-group select="/MailPayload/ActivityPayload" group-by="ProjectOwner">
    <ProjectOwnerWise>
<ProjectOwner>
  <xsl:value-of select="current-grouping-key()"/>
</ProjectOwner>
<Activities>
  <xsl:for-each select="current-group()">
   <IndActivity>
<xsl:copy-of                      select="ActivityId|ActivityName|ProjectId|ProjectName|ReviewerDecision|Comments"/>
   </IndActivity>
  </xsl:for-each>
</Activities>
</ProjectOwnerWise>
</xsl:for-each-group>
   </MailPayload>
  </xsl:template>
</xsl:stylesheet>

****************************************************************************************

oraext:create-nodeset-from-delimited-string


 ****************************************************************************************
<xsl:copy-of select='oraext:create-nodeset-from-delimited-string("{http://www.lauraashley.com/project42/DomainFailedFabricAllocation/CaseLines}CaseLineCollection",string(/client:process/client:Order_line_number),",")'>

****************************************************************************************

Custom Templates in xslt

****************************************************************************************
  </xsl:template> <!--  User Defined Templates  --> 
  <xsl:template name="CalculateTotalRegenOrderValue">
    <xsl:param name="ValueOfOrderLine"/>
    <xsl:param name="recursive_result"/>
    <xsl:choose>
      <xsl:when test="$ValueOfOrderLine">
        <xsl:variable name="OrderDetailType" select="$ValueOfOrderLine[1]"/>
        <xsl:call-template name="CalculateTotalRegenOrderValue">
          <xsl:with-param name="ValueOfOrderLine"
                          select="$ValueOfOrderLine[position() > 1]"/>
          <xsl:with-param name="recursive_result"
                          select="$recursive_result + $OrderDetailType/ns0:unit_price * $OrderDetailType/ns0:task_line_quantity"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$recursive_result"/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>


calling
          <ns1:regenOrderValue>
          <xsl:call-template name="CalculateTotalRegenOrderValue">
            <xsl:with-param name="ValueOfOrderLine"
                            select="/ns0:GetRegenValuesOutputCollection/ns0:GetRegenValuesOutput"/>
            <xsl:with-param name="recursive_result" select="0.0"/>
          </xsl:call-template>
          <!--Here we are using custom function, To calculate the Total Tax Value from salesOrderDetails -->
        </ns1:regenOrderValue>
****************************************************************************************

Copying One Collection at a time in XSL


****************************************************************************************
  <xsl:param name="varCurrentInvoiceIndex"/>
  <xsl:variable name="varInvoiceIndex">
    <xsl:value-of select="$varCurrentInvoiceIndex/inp1:IndexCount/inp1:index"/>
  </xsl:variable>
  <xsl:template match="/">
  <xsl:comment>
        varInvoiceIndex : <xsl:value-of select="$varInvoiceIndex"/>
  </xsl:comment>

    <xsl:for-each select="/ns1:Transaction-810-InvoiceList/ns1:Transaction-810[position() = $varInvoiceIndex]">
      <xsl:copy-of select=".">
        <?oracle-xsl-mapper-position ns1:Transaction-810?>
      </xsl:copy-of>
    </xsl:for-each>
   
  </xsl:template>
</xsl:stylesheet>
****************************************************************************************

Java Embedded for opaque to String

****************************************************************************************
try{    
String inputStr = ((oracle.xml.parser.v2.XMLElement)getVariableData("receiveInput_B2BJMSConsume_Message_InputVariable","body","/ns3:opaqueElement")).getFirstChild().getNodeValue();   
addAuditTrailEntry("Input String : "+inputStr);   
oracle.soa.common.util.Base64Decoder decoder = new oracle.soa.common.util.Base64Decoder();       
String decodedData = decoder.decode(inputStr);   
addAuditTrailEntry("decodedData String : "+decodedData);   
setVariableData("varDecodedStr",decodedData);    
}catch(Exception e){    
}
****************************************************************************************

To Convert Opaque to String (Java Embedded)

****************************************************************************************
try{      
String opaqueDataToString=((oracle.xml.parser.v2.XMLElement)getVariableData("ReceiveCSVFile_Get_InputVariable","opaque","/ns2:opaqueElement")).getFirstChild().getNodeValue();      
addAuditTrailEntry("Input String : "+opaqueDataToString);       
oracle.soa.common.util.Base64Decoder decoder = new oracle.soa.common.util.Base64Decoder();           
String decodedData = decoder.decode(opaqueDataToString);       
addAuditTrailEntry("decodedData String : "+decodedData);       
setVariableData("csvData",decodedData);      
}catch(Exception E){      
   setVariableData("javaFlagVar", "E");       
}
****************************************************************************************

Changing the Namespace

****************************************************************************************
<?xml version="1.0" encoding="ISO-8859-1"?>
<myns:root xmlns:myns="http://zewaren.net/old_location/">
    <myns:jaime_les_patates>
        <myns:super_node> some content </myns:super_node>
    </myns:jaime_les_patates>
</myns:root>

However, the person who wrote it didn't know that the true identifier of the namespace of the document is not http://zewaren.net/old_location/, but rather http://zewaren.net/new_location/. So you need a style sheet to update the url.

<?xml version="1.0" encoding="iso-8859-1"?>
<!-- This sheet replaces the url of a namespace where needed -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:oldmods="http://zewaren.net/old_location/"
    xmlns:myns="http://zewaren.net/new_location/">
     
    <xsl:template match="node()|@*">
        <xsl:copy>
            <xsl:apply-templates select="node()|@*"/>
        </xsl:copy>
    </xsl:template>
     
    <xsl:template match="@oldmods:*">
        <xsl:attribute name="myns:{local-name()}">
            <xsl:value-of select="."/>
        </xsl:attribute>
    </xsl:template>
     
    <xsl:template match="oldmods:*">
        <xsl:element name="myns:{local-name()}">
            <xsl:apply-templates select="node()|@*"/>
        </xsl:element>
    </xsl:template>
     
</xsl:stylesheet>


****************************************************************************************

Javascript for OSB to construct JSON

//Assign variables from pipeline
var $Request = process.actionRequest;

 var $response = {
   "data": {
      "eid": "",
      "approvalMode": "",
      "systemId": "",
      "comments": "",
      "requestId": "",
      "approvalType": "",
      "approvalAction": ""
   }
};

 //Populating json values
//Populating json values
$response.data.eid = $Request.eid.toString();
$response.data.approvalMode = $Request.approvalMode.toString();
$response.data.systemId = $Request.systemId.toString();
$response.data.comments = $Request.comments.toString();
$response.data.requestId = $Request.requestId.toString();
$response.data.approvalType = $Request.approvalType.toString();
$response.data.approvalAction = $Request.approvalAction.toString();
process.actionJSON = $response;
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)