1. Using the Oracle Mapper with Oracle Integration Generation 2
  2. Mapper Use Cases
  3. Perform Data Manipulations in the Mapper

Perform Data Manipulations in the Mapper

You can perform data manipulation tasks in the mapper such as padding characters, rounding numbers, extracting values, adding CDATA content, and calculating the sum of a node.

Pad Characters to a String

  • Left-pad zeroes to nine digits:
    fn:format-number (12345, '000000000')
  • Right-pad zeroes to nine digits:
    concat(string(12345),substring-before(fn:format-number (12345, '000000000'), 
string(12345))

Round a Number to the Required Digits

Round a number to two decimals:

fn:format-number (12345.12345, '#.00')

Extract a Value for a Key-Value Pair Type of XML

XML Snippet XPath Expression Explanation 
<ns:SomeElement xmlns:ns="http://xmlns.oracle.com/some/namespace">
  <ns:ParameterList>
    <ns:Parameter>
      <ns:Name>ID</ns:Name>
      <ns:Value>1</ns:Value>
    </ns:Parameter>
    <ns:Parameter>
      <ns:Name>NAME</ns:Name>
      <ns:Value>Oracle</ns:Value>
    </ns:Parameter>
    <ns:Parameter>
      <ns:Name>AGE</ns:Name>
      <ns:Value>25</ns:Value>
    </ns:Parameter>
  </ns:ParameterList>
</ns:SomeElement>
 Extract the value of parameter name 'NAME': 
/ns:SomeElement/ns:ParameterList
/ns:Parameter[ns:Name='NAME']/ns:Value
 Anything within the set braces in an XPath is called a predicate.

The expression finds the parameter value whose name is NAME.

Add CDATA Content to an XML Element

XSLT Snippet Output XML Explanation
  • Input XML:
    <?xml version = '1.0' encoding = 'UTF-8'?>
<nstrgmpr:process>
 <nstrgmpr:input>
  <nstrgmpr:data>I SHOULD BE IN CDATA CONTENT<nstrgmpr:data>
 </nstrgmpr:input>
</nstrgmpr:process>
  • XSLT snippet:
    <?xml version = '1.0' encoding = 'UTF-8'?>
<xsl:stylesheet version="2.0" xml:id="id_1"  
xmlns:nstrgmpr="http://xmlns.oracle.com/simpleSvc/SyncSvc/Sync" 
xmlns:oracle-xsl-mapper="http://www.oracle.com/xsl/mapper/schemas" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes=" ora oracle-xsl-mapper 
oraext xsi fn xp20 xsl ignore01" 
xmlns:ignore01="http://www.oracle.com/XSL/Transform/java" ignore01:ignorexmlids="true" 
xmlns:xml="http://www.w3.org/XML/1998/namespace">

<xsl:output method="xml" cdata-section-elements="nstrgmpr:result"/>

<xsl:template match="/" xml:id="id_11">
      <nstrgmpr:processResponse xml:id="id_12">

<nstrgmpr:result xml:id="id_16"> <xsl:value-of select="/nstrgmpr:process/nstrgmpr:input/nstrgmpr:data" 
xml:id="id_17"/> </nstrgmpr:result>

      </nstrgmpr:processResponse>
   </xsl:template>
</xsl:stylesheet>
 
<nstrgmpr:processResponse>
 <nstrgmpr:result><![CDATA[I SHOULD BE IN CDATA CONTENT]]></nstrgmpr:result>
</nstrgmpr:processResponse>

You must manually add the xsl:output tag in the XSLT.

The output attribute must be xml.

The attribute cdata-section-elements indicates which fields have the CDATA content post-transformation. In case of multiple elements, the value must be separated by spaces.

The data should be normally mapped to the element in which CDATA should be present.

Note: This approach doesn't work for file-based operations such as stage file actions and FTP where the content is rewritten from XSLT.

Calculate the Sum of a Node from a Group of Nodes

XML Snippet XPath Expression Explanation
  • Input XML
    <ns:SomeElement xmlns:ns="http://xmlns.oracle.com/some/namespace/request">
  <ns:ParameterList>
    <ns:Parameter>
      <ns:Name>ABC</ns:Name>
      <ns:Value>10</ns:Value>
    </ns:Parameter>
    <ns:Parameter>
      <ns:Name>ABC</ns:Name>
      <ns:Value>20</ns:Value>
    </ns:Parameter>
    <ns:Parameter>
      <ns:Name>DEF</ns:Name>
      <ns:Value>20</ns:Value>
    </ns:Parameter>
  </ns:ParameterList>
</ns:SomeElement>
  • XSLT snippet:
    <nstrgdfl:ResultElement>
<xsl:for-each-group select="/ns:SomeElement/ns:ParameterList/ns:Parameter" group-by="ns:Name">
  <nstrgdfl:ParameterList>
   <nstrgdfl:Parameter>
    <nstrgdfl:Name>
      <xsl:value-of select="fn:current-grouping-key()"/>
    </nstrgdfl:Name>
    <nstrgdfl:SumOfValues>
      <xsl:value-of select="sum(fn:current-group/ns:Value)"/>
    </nstrgdfl:SumOfValues>
   <nstrgdfl:Parameter>
  </nstrgdfl:ParameterList>
</xsl:for-each>
</nstrgdfl:ResultElement>
  • Output XML:
    <ns:ResultElement30xmlns:ns="http://xmlns.oracle.com/some/namespace/response">
  <ns:ParameterList>
    <ns:Parameter>
      <ns:Name>ABC</ns:Name>
      <ns:SumOfValues>1</ns:Value>
    </ns:Parameter>
    <ns:Parameter>
      <ns:Name>DEF</ns:Name>
      <ns:SumOfValues>20</ns:Value>
    </ns:Parameter>
  </ns:ParameterList>
</ns:ResultElement>
 The SUM function must be set on current-group.