Processing XML

Some connectors might provide data in XML format. Qlik Application Automation for OEM works with JSON data formats, so a transformation from XML to JSON is needed. This article provides practical examples of how to convert XML to JSON, so that the data can be processed in an automation.

FTP files with XML

Here's an example automation that reads XML from a file on FTP or SFTP:

We use a custom code block to convert the XML to JSON. In this example PHP is used as scripting language:

$xmlArray = $inputs['xml'];
$xmlString = implode($xmlArray);
$jsonString = json_encode(simplexml_load_string($xmlString));
echo $jsonString;

The output of the block Read data from file on FTP is a list with all the lines from the file. Therefore the lines are imploded to one string using implode().

Next, the XML is converted to JSON. The output of the custom code block must be JSON encoded, therefore we use json_encode().

SOAP Webservices with XML

See the paragraph on SOAP Webservices in the article Call URL block for more information on calling a SOAP Webservice and converting the XML response to JSON.

Differences between XML and JSON

The above example code poses a risk. The resulting JSON might be different depending on the number of items with the same tag in the XML.

Example XML 1:

<contacts>
  <contact> <name>John</name> <phone>5555555</phone> </contact>
  <contact> <name>Bob</name> <phone>6666666</phone> </contact>
</contacts>

Example XML 2:

<contacts>
  <contact> <name>Marie</name> <phone>7777777</phone> </contact>
</contacts>

The resulting JSON for the above XMLs will be different:

JSON 1:

{ "contact":
  [
      {"name": "John", "phone": "5555555"},
      {"name": "Bob", "phone": "6666666"}
  ]
}

JSON 2:

{ "contact":
  {"name": "Marie", "phone": "7777777"}
}

As you can see, the value for contact is a list in the first JSON, and an object in the second JSON.

This means that the data structure might be different, depending on the number of results in a specific run of an automation. Therefore it's safer to add code that creates an actual list for certain items. Example PHP code:

$xmlObj = simplexml_load_string($xmlString);
$contactsArr=[];
foreach($xmlObj->children() as $contact) {
  $contactsArr[] = json_decode(json_encode($contact), true);
}
$contacts = json_encode($contactsArr);

Click here for the full PHP example code.

Note that the same might be true for items deeper inside your XML structure. For example, you may have Contacts with Phone numbers, where some contacts have only one <phone> tag in the XML and others have multiple <phone> tags!