Search code examples
xmloracle-databasexsltjdeveloperoracle-soa

Iterate over cursor in Oracle SOA XSL transform

I have a basic application, where I get Loan Products list from database and show to users. I wrote a xsl transform file, where I iterate over resultset and add each row as a Loan Product. But the final List is filled with the same objects.

My composite.xml:

enter image description here

GetLoanProductsBPEL content:

enter image description here

And Transform1 file content inside of GetLoanProductsBPEL component:

enter image description here

In database I have 3 loan products:

enter image description here

But as result I get 3 instances of the same loan product:

<env:Body>
    <inp1:GetLoanProductsResponse xmlns:inp1="http://xmlns.oracle.com/singleString">
        <inp1:LoanProduct>
            <inp1:Id>1</inp1:Id>
            <inp1:Name>Cash loan</inp1:Name>
            <inp1:MaxAmount>100000</inp1:MaxAmount>
            <inp1:InterestRate>12</inp1:InterestRate>
            <inp1:MaxPeriod>60</inp1:MaxPeriod>
        </inp1:LoanProduct>
        <inp1:LoanProduct>
            <inp1:Id>1</inp1:Id>
            <inp1:Name>Cash loan</inp1:Name>
            <inp1:MaxAmount>100000</inp1:MaxAmount>
            <inp1:InterestRate>12</inp1:InterestRate>
            <inp1:MaxPeriod>60</inp1:MaxPeriod>
        </inp1:LoanProduct>
        <inp1:LoanProduct>
            <inp1:Id>1</inp1:Id>
            <inp1:Name>Cash loan</inp1:Name>
            <inp1:MaxAmount>100000</inp1:MaxAmount>
            <inp1:InterestRate>12</inp1:InterestRate>
            <inp1:MaxPeriod>60</inp1:MaxPeriod>
        </inp1:LoanProduct>
    </inp1:GetLoanProductsResponse>
</env:Body>

When I look at Flow Trace, I see that inside Invoke I get 3 different records, but after Transform1 the same record is written 3 times.

Invoke1 trace:

enter image description here

ReplyOutput after transformation:

enter image description here

Transform1 XSLT file source code:

<?xml version="1.0" encoding="UTF-8" ?>
<?oracle-xsl-mapper
 <!-- SPECIFICATION OF MAP SOURCES AND TARGETS, DO NOT MODIFY. -->
 <mapSources>
   <source type="WSDL">
     <schema location="../payments_dwh.wsdl"/>
     <rootElement name="VMbLoanProductsCollection" namespace="http://xmlns.oracle.com/pcbpel/adapter/db/top/payments_dwh"/>
   </source>
 </mapSources>
 <mapTargets>
   <target type="WSDL">
     <schema location="../GetLoanProductsBPEL.wsdl"/>
     <rootElement name="processResponse" namespace="http://xmlns.oracle.com/PashaMobileBankingWs/PashaMobileBankingWS/GetLoanProductsBPEL"/>
   </target>
 </mapTargets>
 <!-- GENERATED BY ORACLE XSL MAPPER 11.1.1.7.0(build 130301.0647.0008) AT [WED OCT 19 17:41:51 AZST 2016]. -->
  ?>
  <xsl:stylesheet version="1.0"
            xmlns:client="http://xmlns.oracle.com/PashaMobileBankingWs/PashaMobileBankingWS/GetLoanProductsBPEL"
            xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
            xmlns:bpws="http://schemas.xmlsoap.org/ws/2003/03/business-process/"
            xmlns:top="http://xmlns.oracle.com/pcbpel/adapter/db/top/payments_dwh"
            xmlns:mhdr="http://www.oracle.com/XSL/Transform/java/oracle.tip.mediator.service.common.functions.MediatorExtnFunction"
            xmlns:bpel="http://docs.oasis-open.org/wsbpel/2.0/process/executable"
            xmlns:oraext="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xmlns:dvm="http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue"
            xmlns:hwf="http://xmlns.oracle.com/bpel/workflow/xpath"
            xmlns:plnk="http://docs.oasis-open.org/wsbpel/2.0/plnktype"
            xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
            xmlns:med="http://schemas.oracle.com/mediator/xpath"
            xmlns:ids="http://xmlns.oracle.com/bpel/services/IdentityService/xpath"
            xmlns:bpm="http://xmlns.oracle.com/bpmn20/extensions"
            xmlns:xdk="http://schemas.oracle.com/bpel/extension/xpath/function/xdk"
            xmlns:xref="http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions"
            xmlns:plt="http://schemas.xmlsoap.org/ws/2003/05/partner-link/"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
            xmlns:ora="http://schemas.oracle.com/xpath/extension"
            xmlns:socket="http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator"
            xmlns:tns="http://xmlns.oracle.com/pcbpel/adapter/db/PashaMobileBankingWs/PashaMobileBankingWS/payments_dwh"
            xmlns:ldap="http://schemas.oracle.com/xpath/extension/ldap"
            exclude-result-prefixes="xsi xsl top plt xsd wsdl tns client plnk xp20 bpws mhdr bpel oraext dvm hwf med ids bpm xdk xref ora socket ldap">
 <xsl:template match="/">
   <client:processResponse>
     <xsl:for-each select="/top:VMbLoanProductsCollection/top:VMbLoanProducts">
       <client:LoanProduct>
         <client:Id>
           <xsl:value-of select="top:id"/>
         </client:Id>
         <client:Name>
           <xsl:value-of select="/top:VMbLoanProductsCollection/top:VMbLoanProducts/top:nameAz"/>
         </client:Name>
         <client:MaxAmount>
           <xsl:value-of select="/top:VMbLoanProductsCollection/top:VMbLoanProducts/top:maxAmount"/>
         </client:MaxAmount>
         <client:InterestRate>
           <xsl:value-of select="/top:VMbLoanProductsCollection/top:VMbLoanProducts/top:interestRate"/>
         </client:InterestRate>
         <client:MaxPeriod>
           <xsl:value-of select="/top:VMbLoanProductsCollection/top:VMbLoanProducts/top:maxPeriod"/>
         </client:MaxPeriod>
       </client:LoanProduct>
     </xsl:for-each>
   </client:processResponse>
 </xsl:template>

Solution

  • Quick answer because I'm replying from my mobile. Remove /top:VMbLoanProductsCollection/top:VMbLoanProducts/ from select attributes inside your for-each loop.