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

Implementation Steps:

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

 
<xsd:schema elementFormDefault="qualified"
targetNamespace="http://www.w3.org/2005/08/addressing"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <xsd:element name="Action" type="xsd:string"/>
</xsd:schema>

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.

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

OIC - How to use DB sequence while insert or merge to database table in Oracle Integration.

Usecase: We will develop an integration with on premise oracle database and insert employee data into database table using a sequence as a unique id as a Primary key.

Logic steps:

1. Create a sequence in the database.
2. Create a Rest Trigger and DB connection.
3. Configure the rest adapter to get the eomployees data.
4. Configure the database adpater >> select operation as "Perform an Operation on a table" >> select Insert or Merge(this 2 only valid) >> select Advanced Options Edit >>  select the created Sequence to fetch the sequence id and insert into the table. 

This is only valid for the insert and merge operarions.

Implementation steps:

Notes:

1. The insert or merge table should have a primary key field that will be assigned from the sequence.
2. The adapter generates sequence numbers in a  batch of 50. Configure sequence in increments of 50. This issue only applies to the oracle database adapter.

Example of a sequence created:

Create Sequence SD_Sequence Start with 1 increment by 50 nominvalue nomaxvalue nocycle nocache;

If we dont follow the notes >> 2nd  step (increment by 50 in sequence), we will get below error:

The sequence named [SD_Squence] is setup incorrectly. Its increment does not match its pre-allocaton size.