Solving the Mapping Problem for Web Service APIs

At Ronin, it’s typical for us to be working on a project that needs to tie multiple systems together. These can be bare metal integrations that work directly against application databases, but more modern integrations will feature some type of web service API (typically REST or SOAP) with the actual payload being JSON or XML data. Today, I thought I’d share a solution I recently used to solve the problem of mapping the payload from one web service API to another.

Mapping API Data

For this particular project, I needed to receive a large web service API payload (a SalesForce event notification with an XML payload), do some interesting internal work (audits, fire off messages to an Azure Service Bus topic, etc.), and finally deliver the payload to another 3^rd party application. Since the incoming payload (XML) would be different than the payload accepted by the 3^rd party application’s web service API (JSON), payload transformation also needed to occur.

There are many ways to pull off this implementation in Azure (HTTP triggered Azure Function App, Azure App Service API, Azure Logic App, etc.), but for this project, we chose Azure Logic Apps. Using Logic Apps would allow the client to make minor tweaks to the process flow without needing us to come back and write additional C# code, which is a bit of a holy grail for many small IT shops requesting project help.

However, the approach did present a problem regarding the payload transformation. For small/simple payloads, it makes sense to use the Logic App JSON creation actions and built-in function expressions, but it gets messy as the payload grows in complexity. Writing some C# code to read a large XML or JSON payload and creating another is relatively simple. This could be placed in an Azure Function App and called from our Logic App, but I’d arrive at a mapping mechanism that would not be tweakable by our client later on.

We used Azure Logic Apps for a previous client to produce EDI flat files. For that implementation, an Azure Integration Account was used to hold maps. These maps described transforming an XML payload to an EDI flat file. That same exact approach would be a bit heavy as it involves navigating custom source XML schemas, using the Microsoft BizTalk mapper (heavy), and in some cases some custom XSLT or BizTalk scriptoid development. 

Still, something like this approach that was lighter weight and integrated with Logic Apps would be a solid fit.

Liquid is Solid

After some research, I stumbled upon the perfect solution for this client: Liquid templates.

Azure Logic Apps have a series of actions for data transformation, pulling the transformation definitions, or maps, from an Azure Integration Account. In addition to BizTalk maps, Integration Accounts natively support Liquid templates (https://shopify.github.io/liquid/). Liquid is an open-source template language that works a bit like XSLT. Using Liquid tags (control flow) and filters (output manipulation), intermediate and some complex JSON and XML transformations can be achieved from an Azure Logic App by editing a simple text file and loading it into an Azure Integration Account.

For example, suppose I have the following incoming XML payload from web service API (e.g. Sales Force):

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
   xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <soapenv:Body>
  <notifications xmlns="http://soap.sforce.com/2005/09/outbound">
   <OrganizationId>00DL0000005xwjAMAQ</OrganizationId>
   <ActionId>04kL00000000BCFIA2</ActionId>
   <SessionId>... session id ...</SessionId>
   <EnterpriseUrl>... url ...</EnterpriseUrl>
   <PartnerUrl>... url ...</PartnerUrl>
   <Notification>
    <Id>12345</Id>
    <sObject xsi:type="sf:Beneficiary__c" xmlns:sf="urn:sobject.enterprise.soap.sforce.com">
     <sf:Id>67890</sf:Id>
     <sf:First_Name__c>Susan</sf:First_Name__c>
     <sf:Last_Name__c>Smith</sf:Last_Name __c>
     <sf:Address_City__c>Atlanta</sf:Address_City__c>
     <sf:Address_State__c>GA</sf:Address_State__c>
     <sf:Phone__c>(678) 867-5309</sf:Phone__c>
     <sf:Primary__c>Yes</sf:Primary__c>
     <sf:Contingent__c>No</sf:Contingent__c>
     <sf:DOB__c>2012-05-11T00:00:00.000Z</sf:DOB__c>
     <sf:Allocation__c>80%</sf:Allocation__c>
    </sObject>
   </Notification>
  </notifications>
 </soapenv:Body>
</soapenv:Envelope>

Further, suppose I need to map this to an outgoing JSON payload for a target web service API as follows:

{
  "Id": 67890,
  "First": "Susan",
  "Last": "Smith",
  "City": "Atlanta",
  "State": "GA",
  "Phone": "6788675309",
  "Status": "CNTG",
  "DoB": "05/11/2012",
  "Allocation": 80.0
}

Notice a couple of wrinkles in this sample mapping:

• I need to strip some formatting from a phone number
• I need to collapse Primary and Contingent elements to a single JSON value
• I need to re-format a date to mm/dd/yyyy

Here’s a Liquid template I would write to get this transformation done:

{
  "Id": {{content.Envelope.Body.notifications.Notification.sObject.Id}},
  "First": "{{content.Envelope.Body.notifications.Notification.sObject.First_Name__c}}",
  "Last": "{{content.Envelope.Body.notifications.Notification.sObject.Last_Name__c}}",
  "City": "{{content.Envelope.Body.notifications.Notification.sObject.Address_City__c}}",
  "State": "{{content.Envelope.Body.notifications.Notification.sObject.Address_State__c}}",

{%- capture formattedPhone -%}
  {{content.Envelope.Body.notifications.Notification.sObject.Phone__c | Strip | Remove: "("  
      | Remove: ")" | Remove: "-" | Remove: "." | Remove: " "}}
{%- endcapture -%}
  "Phone": "{{formattedPhone}}",

{%- if content.Envelope.Body.notifications.Notification.sObject.Primary__c == "Yes" -%}
  "Status": "PRIM",
{%- else -%}
  "Status": "CNTG",
{%- endif -%}

{%- capture extractedYear -%}
  {{content.Envelope.Body.notifications.Notification.sObject.DOB__c | Slice: 0, 4 }}
{%- endcapture -%}
{%- capture extractedMonth -%}
  {{content.Envelope.Body.notifications.Notification.sObject.DOB__c | Slice: 5, 2 }}
{%- endcapture -%}
{%- capture extractedDay -%}
  {{content.Envelope.Body.notifications.Notification.sObject.DOB__c | Slice: 8, 2 }}
{%- endcapture -%}
  "DoB": "{{extractedMonth}}/{{extractedDay}}/{{extractedYear}}",

  "Allocation": {{content.Envelope.Body.notifications.Notification.sObject.Allocation__c 
      | Remove: "%"}}
}

This is just a simple text file I typed up in Notepad++ and then uploaded into an Azure Integration Account:

The Map Type field is key. Instead of using the output XSLT file from BizTalk mapper, I choose Liquid as the type.

Finally, I used the uploaded Liquid template in the Azure Logic App like so:

Real World Web Service Approach

I used this approach to perform all of the XML to JSON payload transformations that were needed for our client’s project. The best part of this approach is its well within their reach to tweak. As more fields are exposed from Sales Force that need to be shuffled down to their target web service API, the client can simply update the map and the JSON parsing.

The approach isn’t without a few issues. A few things I’d consider before using it for another client are:

• The built-in formatting is currently very limited. I had to write my own formatting markup to rework phone numbers and dates. There are a lot of advanced filters out there that would format strings as I needed, but until Microsoft supports registering these extensions, you’re stuck writing your own.
• When you do write your own filtering code, the problem is compounded by the fact that you can’t call a block of code over and over. You end up having to cut and paste any complex formatting. Again, this problem should be solved once Microsoft supports registering extensions.

All-in-all, if you are looking for that in-between mapping solution (easily edited, doesn’t need a lot of complex features, etc.), have a look at Liquid templates!

To learn more about or process, or talk to a Rōnin Software development consultant, contact us today!