Correlated data in Oracle Integration Cloud using XPath Expressions

XPath uses path expressions to select the nodes, elements and attributes in XML payload. You can read about XPath in w3schools.com https://www.w3schools.com/xml/xpath_intro.asp

In this article let’s see how and where we can use XPath in our integrations.

1. Create an App Drive Integration with name “Correlated Payload”. Expose REST adapter as Trigger in integration with verb as POST.
2. Below is the request and response payload used.

Request Payload:

{
   "departments":[
      {
         "deptId":"1",
         "deptName":"IT",
         "deptType":"INTERNAL",
         "employeeCount":"200"
      },
      {
         "deptId":"2",
         "deptName":"HR",
         "deptType":"EXTERNAL",
         "employeeCount":"500"
      },
      {
         "deptId":"3",
         "deptName":"FINANCE",
         "deptType":"EXTERNAL",
         "employeeCount":"100"
      },
      {
         "deptId":"4",
         "deptName":"ADMIN",
         "deptType":"INTERNAL",
         "employeeCount":"400"
      },
      {
         "deptId":"5",
         "deptName":"NETWORK",
         "deptType":"INTERNAL",
         "employeeCount":"800"
      },
      {
         "deptId":"6",
         "deptName":"CLIENTS",
         "deptType":"EXTERNAL",
         "employeeCount":"900"
      }
   ],
   "employees":[
      {
         "empId":"100",
         "empName":"David",
         "deptId":"2"
      },
      {
         "empId":"101",
         "empName":"Adam",
         "deptId":"4"
      }
   ]
}

Response Payload

{
   "employees":[
      {
         "empId":"100",
         "empName":"David",
         "deptId":"2",
         "deptName":"",
         "deptType":""
      },
      {
         "empId":"101",
         "empName":"Adam",
         "deptId":"4",
         "deptName":"",
         "deptType":""
      }
   ]
}

Integration Canvas

Requirement1:

Map employee input information to output. Along with employee information we need department name and department type in the output.

In order to achieve this we open mapper TestCorrelation, map repeating element from Source to Target i.e. Employees to Employees and map Emp Id Emp Name and Dept Id. After mapping it should look like this.

In order to fetch Dept Name and Dept Type we need to join DeptId from Employees node into Department’s node and fetch Dept Name and Dept Type for record with matching Dept Id.

Follow below steps in order to do above task.

a. Create target node for Dept Name in Target.

b. Drag Department’s array from Source to Expression pallet -> Add square brackets -> Drag Dept Id from Department’s array into the expression pallet inside the square bracket

c. Delete the actual path for deptId in the expression pallet -> Drag Dept Id from Employees into expression pallet -> Drag current function from Component Functions pallet(In Node-set).

Expression at this point of time.

d. Drag Dept Name from the Department array and again delete the actual path.

e. Final expression is

/nstrgmpr:execute/nstrgdfl:request-wrapper/nstrgdfl:departments[  nstrgdfl:deptId = current () / nstrgdfl:deptId ] / nstrgdfl:deptName

which says from the departments xml where deptId matches with current loop’s deptId from Employee’s xml for that fetch the deptName. This is the use of XPath expression where using [] we have provided the filter condition.

Another important point to note in this expression is use of current() keyword which is very important to fetch deptId of current employee in the loop.

f. Activate your integration and run your integration.

Output at this point is.

Similarly DeptType can be mapped.

Requirement2:

We want to map output only when DeptType is EXTERNAL and employeeCount is more than 200

Expected output is

For David, DeptType is EXTERNAL and employeeCount is 500 whereas for Adam DeptType is INTERNAL and employeeCount is 400. So only David will be coming in output.

a. Modify integration again. Open mapper and navigate to XSLT view.

b. Add IF construct and add mapping as shown below.

c. Enter expression for IF block as follows.

((/nstrgmpr:execute/nstrgdfl:request-wrapper/nstrgdfl:departments[(nstrgdfl:deptId = current()/nstrgdfl:deptId)]/nstrgdfl:deptType = "EXTERNAL") and (/nstrgmpr:execute/nstrgdfl:request-wrapper/nstrgdfl:departments[(nstrgdfl:deptId = current()/nstrgdfl:deptId)]/nstrgdfl:employeeCount > "100"))

Again above expression uses XPath expression to fetch Node from Departments for current deptId and for that checks the deptType = EXTERNAL and employeeCount > 100.

We have seen that XPath expression can be used

a. Fetch values for individual element from corelated payloads.

b. For checking conditions in IF block in corelated payloads.

c. It can also be used in functions like Count function.

Summary:

In above examples we used simple Request and Response to demonstrate the use of XPath expressions, it can be used in any mappings like DB or FTP. XPath expression can be used in OIC mappers to fetch values from corelated nodes which can then be used to test conditions or to assign values to output elements or can be used in functions.

References:

XPath Tutorial

You can use below link to test you XPath expressions online.

XPath online real-time tester, evaluator and generator for XML & HTML