Zynk Workflow Documentation

This tutorial will help you to convert XML files to CSV format using the XML to CSV task in Zynk.

The XML to CSV Task

On this task you will see the following settings:

In the examples below, we will use the following XML as the input file.

<?xml version="1.0" encoding="utf-8"?>
<Company>
  <SalesOrders>
    <SalesOrder>
      <Id>12345</Id>
      <AccountReference>INTE001</AccountReference>
      <SalesOrderDate>2011-01-01T11:11:11</SalesOrderDate>
      <SalesOrderAddress>
        <Title>Mr</Title>
        <Forename>Test</Forename>
        <Surname>Person</Surname>
        <Address1>i6 Building</Address1>
        <Address2>6-8 Charlotte Square</Address2>
        <Town>Newcastle Upon Tyne</Town>
        <Postcode>NE1 4XF</Postcode>
        <County>County</County>
        <Country>GB</Country>
      </SalesOrderAddress>
      <SalesOrderItems>
        <Item>
          <Sku>TEST01</Sku>
          <QtyOrdered>1</QtyOrdered>
          <UnitPrice>200</UnitPrice>
        </Item>
        <Item>
          <Sku>TEST02</Sku>
          <QtyOrdered>1</QtyOrdered>
          <UnitPrice>200</UnitPrice>
        </Item>
      </SalesOrderItems>
    </SalesOrder>
  </SalesOrders>
</Company>

The Default Mapping

The default mapping will output the values of child nodes and attributes of the nodes returned by the XPath query. If we set the XPath query in the task settings to Company/SalesOrders/SalesOrder, the following output is produced:

Id,AccountReference,SalesOrderDate
12345,INTE001,2011-01-01T11:11:11

Customising the Mapping

If the default mapping does not produce the results you require, a custom mapping can be created to output the data in a specific format. This is done using the Mapping fields setting on the task. When editing the Mapping Fields, you will see a window like the one below.

There are 4 columns, which are used as follows:

The ‘Use Default Mapping’ button can be used to clear all the custom mapping settings and return to the default mapping, if required.

The fields will appear in the output file in the same order as they appear in the table. They can be reordered using the up/down buttons on the right.

The mapping shown will produce the following output:

A/C Ref,Order Date,Address 1
INTE001,01/01/2011,i6 Building

Handling Hierarchical Data 

When your XML file contains hierarchical data, you will need to ‘flatten’ this down in the output CSV file. In order to output each order item onto a separate line in the CSV file, the XPath query in the task settings will need to be set to ‘Company/SalesOrders/SalesOrder/SalesOrderItems/Item’. You will then need to use a custom mapping to output the data correctly.

Notice how ‘../’ is used in the XPath queries to move back to the parent node. This is required to access the Id, AccountReference and SalesOrderAddress, as they belong to a parent of the Item nodes. Taking the Id as an example, the query executed would be ‘Company/SalesOrders/SalesOrder/SalesOrderItems/Item/../../Id’. The ‘../../’ moves from the Item node back up to the SalesOrder node, which is where the Id is found.

The mapping shown will produce the following output:

ID,A/C Ref,Name,Sku,Quantity,Price
12345,INTE001,Test,TEST01,1,£200.00
12345,INTE001,Test,TEST02,1,£200.00

Using Predicates

Furthermore, you can use predicates to access specific nodes within the mapping. For example, say you have the below XML.

<?xml version="1.0" encoding="utf-8"?>
<Company xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Customers>
    <Customer>
      <AccountReference>A1D001</AccountReference>
      <CompanyName>A1 Design Services</CompanyName>
      <CustomFields>
        <CustomField>
          <Name>FIRST_INV_DATE</Name>
          <Value>02/01/2015 00:00:00</Value>
        </CustomField>
        <CustomField>
          <Name>LAST_INV_DATE</Name>
          <Value>15/03/2015 00:00:00</Value>
        </CustomField>
      </CustomFields>
    </Customer>
  </Customers>
</Company>

And you only wanted to retrieve the LAST_INV_DATE custom field, in your custom mapping you could use CustomFields/CustomField[Name='LAST_INV_DATE']/Value to retrieve the desired value.


Back | Edit on Github