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)" />

Example:

Input: Note: abcdefg1234

Output: 1234

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="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="urn:UserWSDL" xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:weo="http://www.example.org">

<types>

<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="Users">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="User" maxOccurs="unbounded">

<xsd:complexType>

<xsd:sequence>

<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"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

</types>

<portType name="UserServicePort">

<operation name="UserGetOP">

<input message="tns:UserRequestPort"/>

</operation>

</portType>

<message name="UserRequestPort">

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

</message>

</definitions>

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:

https://soalicious.blogspot.com/2022/02/xsd-opaque-schema.html

Users.json:

{

"Users":{

"User":[

{

"id": 1,

"firstName":"string",

"lastName":"string",

"email":"string",

"country":"string"

},

{

"id": 2,

"firstName":"string",

"lastName":"string",

"email":"string",

"country":"string"

},

{

"id": 3,

"firstName":"string",

"lastName":"string",

"email":"string",

"country":"string"

}

]

}

}

Used oneway wsdl:

https://soalicious.blogspot.com/2023/08/wsdl-oneway-users-wsdl-file.html

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 - Handling Custom SOAP Headers with XML Schema in Oracle Integration Cloud (OIC)

Usecase: 

In many SOAP-based integrations, the service provider requires additional WS-Addressing headers (such as Action and To) to be explicitly included in the SOAP request.

However, OIC does not always auto-generate these headers, and missing them can lead to authentication or routing errors.

The challenge:

SOAP web service expects custom WS-Addressing headers (Action, To).

OIC does not allow adding them directly in the standard mapper.

We need a way to inject these headers in the SOAP request payload dynamically.

The following highlighted Action and To headers need to pass:

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005...>

<s:Header>

<a:Action s:mustUnderstand="1">http://www.testsoftwate.com/dataservices/bidata/2/testdataservice</a:Action>

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

</s:Header>

<s:Body>

...

</s:Body>

</s:Envelope>

Solution

We solve this problem by defining a custom XML schema for the required headers and attaching it to the SOAP Header section in OIC.

Implementation Steps:

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

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:wsa="http://www.w3.org/2005/08/addressing"
           targetNamespace="http://www.w3.org/2005/08/addressing"
           xmlns="http://www.w3.org/2005/08/addressing"
           elementFormDefault="qualified">

    <xs:element name="Action" type="xs:string"/>
    <xs:element name="To" type="xs:string"/>
</xs:schema>

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

Note: For each element in the SOAP header, the schema must be imported separately. In this case, since we have two elements (Action and To), the schema needs to be imported twice, and each element has to be selected individually.

Step3: Map the Header value in the mapper.

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/soap-adapter/configure-custom-soap-headers-soap-adapter.html#GUID-D21009B4-1E69-4D57-3EA892EF31F3

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.

Steps:

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="http://www.w3.org/2001/XMLSchema" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns:tns="http://TargetNamespace.com/fileReference" targetNamespace="http://TargetNamespace.com/fileRefeference" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars"

nxsd:fieldValidation="true"

 nxsd:encoding="US-ASCII">

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

<xsd:complexType>

<xsd:sequence>

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

<xsd:complexType>

<xsd:sequence>

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

<xsd:simpleType>

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

<xsd:minLength value="1"/>

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

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

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

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

=SUMIF(G2:G366,"<0")

Output: -2590

To sum positive numbers

=SUMIF(G2:G366,">0")

Output: 2590