OIC - Handling Base64 Encoded JSON NXSD Parsing Issue in Oracle Integration Cloud (OIC)

📌 Problem Statement

In a Real-Time REST integration in Oracle Integration Cloud, the source system sends Base64 encoded JSON.

Flow:

Receive Base64 payload

Decode using oraext:decodeBase64()

Pass to Stage File → Read as JSON

❗ Issue Faced

After decoding, the JSON becomes invalid because:

The closing curly brace } is missing

This causes NXSD parsing errors in Stage File

🔍 Root Cause

During decoding or transformation:

Extra whitespace / formatting issues

Improper encoding at source

OIC string handling edge cases

👉 Result: JSON becomes malformed, especially missing }

💡 Solution Approach

Instead of directly parsing decoded JSON:

✅ Step 1: Decode Base64

✅ Step 2: Clean the JSON string

✅ Step 3: Validate & fix missing closing brace

✅ Step 4: Re-encode to Base64

✅ Step 5: Pass as Opaque to Stage File

🛠️ Implementation (XSLT Logic)

✅ Step 1: Decode Base64

Xslt

<xsl:variable name="decoded"

 select="oraext:decodeBase64(/nssrcmpr:execute/ns16:request-wrapper/ns16:message)"/>

✅ Step 2: Clean unwanted whitespace

Xslt

<xsl:variable name="cleaned"

  select="replace($decoded, '(:\s*&quot;)\s+', '$1')"/>

👉 Removes unnecessary spaces after : in JSON

✅ Step 3: Fix Missing Closing Brace

Xslt

<xsl:variable name="finalJson">

  <xsl:choose>

    <xsl:when test="contains($cleaned, '}')">

      <xsl:value-of select="$cleaned"/>

    </xsl:when>

    <xsl:otherwise>

      <xsl:value-of select="concat($cleaned, '}')"/>

    </xsl:otherwise>

  </xsl:choose>

</xsl:variable>

👉 Ensures JSON is always valid

✅ Step 4: Encode Back to Base64

Xslt

<xsl:value-of select="oraext:encodeBase64($finalJson)"/>

🔄 Integration Flow Design

🧩 OIC Flow Steps

REST Trigger

Stage File (Write File)

Write as Opaque

Use above XSLT

Stage File (Read File)

Now JSON is valid

Parse using NXSD schema

Continue processing…

🎯 Why Opaque Handling Works

Using opaque avoids:

Early validation failures

NXSD parsing errors on invalid JSON

👉 You fix JSON before parsing

⚠️ Best Practices

Always validate decoded payload:

Use contains() or ends-with() for }

Log decoded payload (for debugging)

Avoid direct parsing of decoded Base64 without validation

🏁 Conclusion

Handling Base64 JSON in OIC can be tricky due to:

Encoding inconsistencies

Transformation side effects

👉 The decode → clean → fix → re-encode → stage as opaque pattern is a reliable solution.

Code screenshots:

Stage write:

Code xslt:

Read json:

Code snippet:

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

  <nstrgmpr:Write xml:id="id_12">

    <ns31:opaqueElement>

      <!-- Step 1: Decode -->

      <xsl:variable name="decoded"     select="oraext:decodeBase64(/nssrcmpr:execute/ns16:request-wrapper/ns16:message)"/>

      <!-- Step 2: Clean whitespace after ":" -->

      <xsl:variable name="cleaned"

        select="replace($decoded, '(:\s*&quot;)\s+', '$1')"/>

      <!-- Step 3: Check and fix closing brace -->

      <xsl:variable name="finalJson">

        <xsl:choose>

          <xsl:when test="contains($cleaned, '}')">

            <xsl:value-of select="$cleaned"/>

          </xsl:when>

          <xsl:otherwise>

            <xsl:value-of select="concat($cleaned, '}')"/>

          </xsl:otherwise>

        </xsl:choose>

      </xsl:variable>

      <!-- Step 4: Encode back -->

      <xsl:value-of select="oraext:encodeBase64($finalJson)"/>

    </ns31:opaqueElement>

  </nstrgmpr:Write>

</xsl:template>

OIC - Handling Multiple Stub & Payment Matching in XSLT (OIC Optimization)

🚀 Handling Multiple Stub & Payment Matching in XSLT (OIC Optimization)

📌 Problem Context

We receive:

Multiple StubRecords

Multiple PaymentRecords

Same Transaction Number across records

👉 Goal:

Correctly match multiple stubs with multiple payments

Ensure accurate mapping

Avoid 120 sec XSLT timeout

⚠️ Issue with Traditional Matching

XML

//ns25:PaymentRecord[ns25:TransactionNumber = $txn]

❌ Fetches all records

❌ Incorrect matching

❌ Slow → Timeout

✅ Optimized Steps Using xsl:key

🔑 Step 1: Define Keys

XML

<xsl:key name="stubKey"

         match="ns25:StubRecord"

         use="concat(ns25:TransactionNumber,'|',ns25:OperatorID)"/>

<xsl:key name="payKey"

         match="ns25:PaymentRecord"

         use="concat(ns25:TransactionNumber,'|',normalize-space(ns25:Filler3))"/>

<xsl:key name="payKeyExact"

         match="ns25:PaymentRecord"

         use="concat(ns25:TransactionNumber,'|',normalize-space(ns25:Filler3),'|',ns25:PaymentAmount)"/>

🔄 Step 2: Loop Through Stub Records

XML

<xsl:for-each select="$ReadSourceFile/.../ns25:StubRecord">

🧮 Step 3: Extract Values

XML

<xsl:variable name="txn" select="ns25:TransactionNumber"/>

<xsl:variable name="op" select="ns25:OperatorID"/>

<xsl:variable name="amt" select="ns25:PaidAmount"/>

🔗 Step 4: Build Composite Keys

XML

<xsl:variable name="stubKeyVal" select="concat($txn,'|',$op)"/>

<xsl:variable name="payKeyVal" select="concat($txn,'|',$op)"/>

<xsl:variable name="payKeyExactVal" select="concat($txn,'|',$op,'|',$amt)"/>

⚡ Step 5: Fetch Using Key

XML

<xsl:variable name="sameStub" select="key('stubKey',$stubKeyVal)"/>

<xsl:variable name="payments" select="key('payKey',$payKeyVal)"/>

<xsl:variable name="paymentsExact" select="key('payKeyExact',$payKeyExactVal)"/>

🔁 Step 6: Handle Multiple Stub Records

XML

<xsl:if test="count($sameStub) > 1">

👉 Ensures:

Only process when multiple stubs exist for same transaction + operator

Avoid incorrect or duplicate mapping

🎯 Step 7: Choose Best Payment Match

XML

<xsl:variable name="finalPayments"

    select="if (exists($paymentsExact)) then $paymentsExact else $payments"/>

👉 Prefer:

Exact match (txn + operator + amount)

Else fallback

🔄 Step 8: Map & Merge Data

XML

<xsl:value-of select="normalize-space(ns25:StubAccountNumber)"/>

<xsl:value-of select="oraext:create-delimited-string($finalPayments/ns25:ChequeNumber,'|')"/>

📤 Step 9: Generate Output

👉 One output per valid stub, enriched with matched payments

🎯 Final One-Line Summary

👉 “Use xsl:key with composite keys to accurately match multiple stub and payment records for the same transaction, handle multi-stub scenarios using count logic, and ensure optimized performance to avoid XSLT timeout in OIC.”

Code Screenshots:

OIC - Optimizing XSLT Transformations Using xsl:key | Muenchian grouping

🚀 Optimizing XSLT Transformations Using xsl:key

(With Real Integration Example from OIC)

When working with large XML payloads in Oracle Integration Cloud (OIC) or any XSLT-based transformation, performance and duplicate handling become critical. One powerful but often underused feature is xsl:key, which enables fast lookups and efficient grouping.

Let’s break down your scenario and understand it step by step.

📌 Problem Context

👉 ““We need to uniquely match payment records with stub records using transaction number and operator, and merge their details efficiently. Using traditional XPath-based conditional matching causes repeated XML scans, increasing processing time and leading to XSLT timeout issues (120 seconds) in OIC. Our goal is to optimize this using xsl:key for faster lookup and better performance.”

You are processing payment records like:

XML

ns24:Payments/ns24:PaymentRecord

Each record contains:

TransactionNumber

OperatorID

PaymentMethod

PaymentAmount

etc.

👉 The goal is to:

Identify unique records

Avoid duplicates

Map values efficiently

Improve transformation performance

Steps for resolution with key(): 

🔍 Step 1: Understanding the Key Definition

XML

<xsl:key name="stub-by-txn-op"

         match="ns24:StubRecord"

         use="concat(ns24:TransactionNumber, '|', ns24:OperatorID)"/>

💡 What this does:

Creates an index (like a hash map) on StubRecord, where the key is a combination of TransactionNumber and OperatorID (e.g., TX123|OP456) for fast lookup.

✅ Why important:

Enables O(1) lookup. Avoids looping through entire XML repeatedly

🔍 Step 2: Iterating Over Payment Records

XML

<xsl:for-each select="$ReadSourceFile/.../ns24:PaymentRecord[

  count(. | key('stub-by-txn-op', concat(ns24:TransactionNumber,'|',normalize-space(ns24:Filler3)))) = 1

]">

💡 What’s happening here:

👉 Uses Muenchian grouping with xsl:key to fetch matching stub records and ensure only unique records are processed, efficiently eliminating duplicates.

🔍 Step 3: Variable Creation for Lookup

XML

<xsl:variable name="txn" select="ns24:TransactionNumber"/>

<xsl:variable name="op" select="normalize-space(ns24:Filler3)"/>

👉 These variables:

Store values for reuse

Improve readability

Avoid repeated XPath evaluation

🔍 Step 4: Fetch Matching Stub Record

XML

<xsl:variable name="stub"

    select="key('stub-by-txn-op', concat($txn,'|',$op))"/>

💡 Key Benefit:

Direct lookup instead of looping

Much faster than:

XML

//ns24:StubRecord[TransactionNumber=$txn and OperatorID=$op]

🔍 Step 5: Data Mapping Logic

Example: Payment Mode

XML

<xsl:choose>

  <xsl:when test="ns24:PayMethod='C'">CASH</xsl:when>

  <xsl:when test="ns24:PayMethod='N'">CHEQUE</xsl:when>

</xsl:choose>

👉 Converts coded values into business-friendly values.

Example: Payment Source

XML

<xsl:choose>

  <xsl:when test="ns24:PayMethod='C'">HKPOSTCS</xsl:when>

  <xsl:when test="ns24:PayMethod='N'">HKPOSTCQ</xsl:when>

</xsl:choose>

Example: Account Number from Key Lookup

XML

<xsl:value-of select="normalize-space($stub/ns24:StubAccountNumber)"/>

👉 This uses the key-based lookup result.

Example: Amount Formatting

XML

<xsl:choose>

  <xsl:when test="number(ns24:PaymentAmount)=0">0.00</xsl:when>

  <xsl:otherwise>

    <xsl:value-of select="format-number(ns24:PaymentAmount div 100,'#.00')"/>

  </xsl:otherwise>

</xsl:choose>

👉 Converts amount into proper decimal format.

⚡ Why xsl:key is Critical in OIC

❌ Without xsl:key

Nested loops

Slow performance (O(n²))

Difficult to maintain

✅ With xsl:key

Fast lookup (O(1))

Clean logic

Scales for large payloads

📊 Real Impact in OIC Integrations

Scenario - 10,000 records

Without Key: Slow. Lookup logic: Complex

With Key: Fast lookup logic; Simple

🧠 Best Practices

✔ Always use xsl:key when:

Matching records across nodes

Handling large XML files

Avoiding nested loops

✔ Use composite keys:

XML

concat(field1, '|', field2)

✔ Normalize data:

XML

normalize-space()

🎯 Final Thoughts

Your implementation is a textbook example of efficient XSLT design:

Uses Muenchian grouping

Implements fast lookup via keys

Ensures clean and scalable transformation

Screenshot of source code: