Tuesday, July 31, 2012

Dealing with Multiple Sources in XSLT in SOA 11g

Quite often we need to derive the values into a variable(target) from more than single source.
SOA 11g Transform activity provides us a way to tag multiple sources in the wizard itself as shown below.


Where source1Var and source2Var are two different sources. If you observe closely in the source code for the same transform activity , the code snapshot pasted below



            <copy>
                <from>ora:doXSLTransformForDoc("xsl/XformSource1Source2ToTarget.xsl", $source1Var, "source2Var", $source2Var)</from>
                <to variable="targetVar"/>
            </copy>

The first source(source1Var) is passed as the normal source to XSL where as the second one is sent as a parameter to the same with the name source2Var and the value of the same is $source2Var.

Also if you open the xsl in source mode you can find that 
....
  <xsl:param name="source2Var"/>
....
So now you can use this second source ( anything other than primary source) as a parameter inside the xslt mapping.

Now lets see how we can work on multiple sources in XSLT. Lets say you have requirement like this.

1. Some of Target variables data elements depend on source1var.
2. Some of them depend on source2Var.
3. Some of them are dependent on source1Var and source2Var in a complex way. ie.,Lets assume a scenario such as -
Loop through the source2 for each record of source1 , then find that record whose "id"(of source2) matches with "id" of source1.Now then multiply   source1Var/x  element with the fetched record of source2Var/y.

1,2 are pretty straightforward with the mapping done directly. 
Coming to scenario 3 we may need to tweak xslt a little bit like below by using some local variables.

         <xsl:for-each select="source1Var/ yourComplexElementForSource1">
             <xsl:variable name="id" select="id"/>
              <xsl:variable name="x" select="x"/>
              <xsl:variable name="y" select="$source2Var/yourComplexElementForSource2[id= $id)]/y"/>
          <targetVarElement>
            <xsl:value-of select="$x * $y"/>
          </targetVarElement>
        </xsl:for-each>

If you observe we are using an inline xpath matching of the value id  of source2(id) with id of source1($id). This acts like another loop(on source2's complex element) for us ( avoiding a real loop to be written).
So thats it ! It works.

A related question is posted on oracle forums recently- https://forums.oracle.com/forums/thread.jspa?threadID=2421631&tstart=0
Please find below is the solution for the same using the above mentioned approach.

A Sample implementation of the concept exaplained above: 

source1.xsd

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns="http://www.example.org"
            targetNamespace="http://www.example.org"
            elementFormDefault="qualified">
  <xsd:element name="docTypeRef_tns_RetrieveGetDataResponse">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="instrumentDatas">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="instrumentData" maxOccurs="unbounded">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element name="instrument">
                      <xsd:complexType>
                        <xsd:sequence>
                          <xsd:element name="id" type="xsd:string"/>
                          <xsd:element name="yellowkey" type="xsd:string"/>
                        </xsd:sequence>
                      </xsd:complexType>
                    </xsd:element>
                    <xsd:element name="data" maxOccurs="unbounded">
                      <xsd:complexType>
                        <xsd:attribute name="value" type="xsd:string"/>
                      </xsd:complexType>
                    </xsd:element>
                  </xsd:sequence>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

source2.xsd

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns="http://www.example.org"
            targetNamespace="http://www.example.org"
            elementFormDefault="qualified">
  <xsd:element name="MyElement">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="TmsBmbrgRateslist" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="curcyPair" type="xsd:string"/>
              <xsd:element name="rate" type="xsd:integer"/>
              <xsd:element name="rDate" type="xsd:integer"/>
              <xsd:element name="attrib1" type="xsd:string"/>
              <xsd:element name="attrib2" type="xsd:string"/>
              <xsd:element name="attrib3" type="xsd:integer"/>
              <xsd:element name="attrib4" type="xsd:string"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="xsi" type="xsd:string"/>
      <xsd:attribute name="schemaLocation" type="xsd:string"/>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

target.xsd

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns="http://www.example.org"
            targetNamespace="http://www.example.org"
            elementFormDefault="qualified">
  <xsd:element name="F1113Collection">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="F1113" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="c1Rtty" type="xsd:string"/>
              <xsd:element name="c1Crdc" type="xsd:string"/>
              <xsd:element name="c1Crcd" type="xsd:string"/>
              <xsd:element name="c1Crr" type="xsd:string"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
      <xsd:attribute name="ns2" type="xsd:string"/>
      <xsd:attribute name="xsi" type="xsd:string"/>
      <xsd:attribute name="schemaLocation" type="xsd:string"/>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Requirement :

Populate "c1Crr" field of target based on below logic.

if(<instrumentData>/<instrument>/<id> = <TmsBmbrgRateslist>/<curcyPair>)
{
c1Crr = <instrumentData>/data[2]/@value * <TmsBmbrgRateslist>/attrib3
}

XSLT Mapping:

<?xml version="1.0" encoding="UTF-8" ?>
<?oracle-xsl-mapper
  <!-- SPECIFICATION OF MAP SOURCES AND TARGETS, DO NOT MODIFY. -->
  <mapSources>
    <source type="XSD">
      <schema location="../xsd/source1.xsd"/>
      <rootElement name="docTypeRef_tns_RetrieveGetDataResponse" namespace="http://www.example.org"/>
    </source>
    <source type="XSD">
      <schema location="../xsd/source2.xsd"/>
      <rootElement name="MyElement" namespace="http://www.example.org"/>
      <param name="source2Var" />
    </source>
  </mapSources>
  <mapTargets>
    <target type="XSD">
      <schema location="../xsd/target.xsd"/>
      <rootElement name="F1113Collection" namespace="http://www.example.org"/>
    </target>
  </mapTargets>
  <!-- GENERATED BY ORACLE XSL MAPPER 11.1.1.5.0(build 110418.1550.0174) AT [WED AUG 01 02:09:19 IST 2012]. -->
?>
<xsl:stylesheet version="1.0"
                xmlns:bpws="http://schemas.xmlsoap.org/ws/2003/03/business-process/"
                xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
                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:ns0="http://www.example.org"
                xmlns:dvm="http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue"
                xmlns:hwf="http://xmlns.oracle.com/bpel/workflow/xpath"
                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:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"
                xmlns:ora="http://schemas.oracle.com/xpath/extension"
                xmlns:socket="http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator"
                xmlns:ldap="http://schemas.oracle.com/xpath/extension/ldap"
                exclude-result-prefixes="xsi xsl ns0 xsd bpws xp20 mhdr bpel oraext dvm hwf med ids bpm xdk xref bpmn ora socket ldap">
  <xsl:param name="source2Var"/>
  <xsl:template match="/">
    <ns0:F1113Collection>
      <xsl:for-each select="/ns0:docTypeRef_tns_RetrieveGetDataResponse/ns0:instrumentDatas/ns0:instrumentData">
        <ns0:F1113>
          <ns0:c1Rtty>
            <xsl:value-of select='string("A")'/>
          </ns0:c1Rtty>
          <ns0:c1Crdc>
            <xsl:value-of select="substring(ns0:instrument/ns0:id,4,3)"/>
          </ns0:c1Crdc>
          <ns0:c1Crcd>
            <xsl:value-of select="substring(ns0:instrument/ns0:id,1,3)"/>
          </ns0:c1Crcd>
          <xsl:variable name="id" select="ns0:instrument/ns0:id"/>
          <xsl:variable name="dataValue" select="ns0:data[2]/@value"/>
          <xsl:variable name="attrib3"
                        select="$source2Var/ns0:MyElement/ns0:TmsBmbrgRateslist[(ns0:curcyPair = $id)]/ns0:attrib3"/>
          <ns0:c1Crr>
            <xsl:value-of select="$dataValue * $attrib3"/>
          </ns0:c1Crr>
        </ns0:F1113>
      </xsl:for-each>
    </ns0:F1113Collection>
  </xsl:template>
</xsl:stylesheet>

References:
1. http://blogs.oracle.com/soa_how_to/entry/how_to_implement_multi-source_xslt_mapping_in_11g_bpel
2. http://java.net/downloads/oraclesoasuite11g/Transformations/mapper-105-multiple-sources.zip

5 comments:

  1. based on the above lines, i have a requirement where 1 source contains employee records and other source contains employee and dependent records. They will be joined based on the SSN no. I need to generate target with employee and multiple dependents. Currently with above approach, the target file is only has Employee and 1 dependent. It is not generating for other dependents
    Ex: Source 1 record: ESSN, DOB, DTHIRE etc
    Source 2 record: ESSN, DOB, MEDSTATUS, ACTDTAT etc
    DSSN, DOB, ESSN, NEWSTAT, BUILDSTAT etc
    DSSN1, DOB1, ESSN, NEWSTAT1, BUILDSTAT1 etc

    the target should contain:
    Layout1: ESSN, DOB, MEDSTATUS, ACTDTAT etc
    Layout2: DSSN, DOB, ESSN, NEWSTAT, BUILDSTAT etc
    DSSN1, DOB1, ESSN, NEWSTAT1, BUILDSTAT1 etc

    ReplyDelete
  2. Useful information, thank you.

    I am wondering whether it is possible to pass parameters into a transform in a routing rule in Mediator in Soa SUite 11g, or can it only be done within Bpel?

    thanks
    Lisa

    ReplyDelete
  3. Hello Sridhar, I have a similar requirement in my xslt. I have been able to add multiple sources to the xslt. While I'm building the my SOA Composite, am getting the below exception.

    Error(199): Namespace prefix "" qualifying "inputVariable.payload" cannot be resolved.

    Below is my analysis so far with this issue
    ---------------------------------------------
    1) Am facing this compilation issue when I build the SOA Composite in the version 11.1.1.7.0. Composite compiles properly in 11.1.1.6.0
    2) The compiler is able to identify the first source properly but it is not recognizing the second , third etc properly and it complains in all the places about namespace not resolved.

    Note : Our build server uses the SOA 11.1.1.7.0 libraries to build the composites. But the project compiles properly on the 11.1.1.6.0 jdeveloper properly.

    Can you please shade some light on this?

    Regards
    Ashok

    ReplyDelete
  4. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please visit https://oracleappsfusion.teachable.com

    ReplyDelete