0006 How to Transform a Complex XML file

A practical Advanced ETL Processor example for building, testing, and adapting one automation workflow.

Advanced ETL Processor

4.9 ★★★★★ Based on 16 reviews on Capterra See all reviews on Capterra →

Use this example to build one working automation package, test it safely, and then adapt it to your own files, folders, or data rules. Start small. Automation is helpful; automated mistakes are just faster mistakes.

This Example Demonstrates how to transform Complex XML into a simple, more readable format

The problem with XML

Here is an XML example, it is very well structured therefore there is no need to perform additional transformations.

<Table>
<Record>
<ID>1</ID>
<Company>James Bond Production</Company>
<Amount>13</Amount>
</Record>
<Record>
<ID>2</ID>
<Company>Green Cloud</Company>
<Amount>14</Amount>
</Record>
</Table>

However there is no guarantee that the next file will have exactly the same structure, for example, this XML has a different tag order.

<Table>
<Record>
<Company>James Bond Production</Company>
<ID>1</ID>
<Amount>13</Amount>
</Record>
<Record>
<ID>2</ID>
<Company>Green Cloud</Company>
<Amount>14</Amount>
</Record>
</Table>

This XML has additional YEAR tag

<Table>
<Record>
<ID>1</ID>
<Company>James Bond Production</Company>
<Year>1956</Year>
<Amount>13</Amount>
</Record>
<Record>
<ID>2</ID>
<Company>Green Cloud</Company>
<Amount>14</Amount>
</Record>
</Table>

This kind of of problems can be easily addressed by using XSLT:

<?xml version=“1.0” encoding=“UTF-8”?>
<xsl:stylesheet version=“1.0” xmlns:xsl=“http://www.w3.org/1999/XSL/Transform”>
<xsl:output method=“xml” indent=“yes” version=“1.0”/>
<xsl:template match=“Table”>
<Table>
<xsl:for-each select=“Record”>
<Record><ID><xsl:value-of select=“ID”/></ID>
<Company><xsl:value-of select=“Company”/></Company>
<Amount><xsl:value-of select=“Amount”/></Amount>
</Record>
</xsl:for-each>
</Table>
</xsl:template>
</xsl:stylesheet>

Note: XSLT is a way of transforming XML into a different format