Thursday, August 17, 2023

XSLT - How to get last 4 characters only from a substring with variable length of characters each time

We can use the string-length() in conjunction with substring to get the last 4 characters

<xsl:value-of select="substring(cbc:Note, string-length(cbc:Note) - 3)" />


Input: Note: abcdefg1234

Output: 1234

Friday, August 11, 2023

WSDL links

Wsdl links:
  1. Abstract wsdl vs Concrete wsdl
  2.  Oneway Message Pattern WSDL
  3. WSDL - Asyn Callback Service | Async BPEL vs Sync BPEL
  4. Sample Calculator WSDL
  5. Oneway employee wsdl and imported XSD

WSDL - Oneway users wsdl file

Oneway users Wsdl file:

<?xml version="1.0" encoding="UTF-8"?>

<definitions targetNamespace="urn:UserWSDL" xmlns="" xmlns:tns="urn:UserWSDL" xmlns:soap12="" xmlns:mime="" xmlns:xsd="" xmlns:soap="" xmlns:weo="">

<xsd:schema xmlns:xsd="" xmlns="" targetNamespace="" elementFormDefault="qualified">

<xsd:element name="Users">
<xsd:element name="User" maxOccurs="unbounded">
<xsd:element name="id" type="xsd:integer"/>
<xsd:element name="firstName" type="xsd:string"/>
<xsd:element name="lastName" type="xsd:string"/>
<xsd:element name="email" type="xsd:string"/>
<xsd:element name="country" type="xsd:string"/>

<portType name="UserServicePort">
<operation name="UserGetOP">
<input message="tns:UserRequestPort"/>

<message name="UserRequestPort">
<part name="part" element="weo:Users"/>

OIC - How to convert XML data to Json data and store it in a variable

Usecase: Here we have a requirement that Source will send xml payload using our exposed or triggered SOAP API and then we will convert it to JSON format and store it in a variable to insert the json data into a database table.

Logic steps:

  1. Create a WSDL file and create a SOAP trigger connection.
  2. Create a App driven orcehstration  and configure with the created soap connection
  3. Take a stage and write file with json sample and map the xml data to json data.
  4. Take stage again and read the json file using opaque schema
  5. Take a assign and create a variable and store the read file reference with decodebase64().
  6. Add tracking, save, activate , copy the wsdl and open a project in soap ui tool and add basic authentication and test

Opaque xsd:






"id": 1,







"id": 2,







"id": 3,









Used oneway wsdl:

Detailed with screenshots:

Soap connection:

Integration flow:

Configure soap trigger

Stage to write xml to json file

Read the json file as opaque:

Take assign and map the read file reference with decodebase64() inbuilt function.

Test from SOAPUI

Monitoring >> activity stream

OIC - add custom HTTP headers invoking to external SOAP service

Usecase: Here, we will show you how to send or configure a custom HTTP header while invoking a External SOAP service.

The following highlighted Action header need to pass:

<s:Envelope xmlns:s="" xmlns:a=">


<a:Action s:mustUnderstand="1"></a:Action>

<a:To s:mustUnderstand="1">http://www.servivehost/services/testdataservice</a:To>






Implementation Steps:

Step1: Create header schema for the custom SOAP headers as below:

<xsd:schema elementFormDefault="qualified"
        <xsd:element name="Action" type="xsd:string"/>

Step2: save this as ActionHeader.xsd and checked the custom header option for request and upload the file to invoke configure adapter.

Step3: Map the Header value in the mapper.

Friday, August 4, 2023

Excel links

Excel links:
  1.  Excel - How to sum of all positve numbers or negative numbers from a range of values
  2. MS Excel - Formatting tips and tricks for better project management
  3. MS Excel - Add drop down list and add conditional coloring or formatting

OIC XSD - How to validate if any field is empty while reading the file | add restriction to a schema field

Usecase: Here, we are reading a file. During reading, we need to validate field C1 if it is empty . If the any of the field C1 is empty, we have to fail it from further processing.


  1. Add attribute nxsd:fieldValidation="true" to the schema.
  2. Add the restriction minLength value="1" block to the field to validate.

Schema used:

<?xml version="1.0" encoding="UTF-8" ?>

<xsd:schema xmlns:xsd="" xmlns:nxsd="" xmlns:tns="" targetNamespace="" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars"



<xsd:element name="Root-Element">



<xsd:element name="Employee" minOccurs="1" maxOccurs="unbounded">



<xsd:element name="C1" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="&quot;">


<xsd:restriction base="xsd:string">

<xsd:minLength value="1"/>




<xsd:element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="&quot;"/>








Thursday, August 3, 2023

Excel - How to sum of all positve numbers or negative numbers from a range of values

Usecase: we have a range of numbers in excel file, we have to sum all positive numbers and negative numbers separately from that range.

Steps: Use SUMIF() function to achieve this requirment:

To sum positive numbers


Output: -2590

To sum positive numbers


Output: 2590

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...