0006 How to Transform a Complex XML file

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 not 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 guarantie that next file will have exactly the same structure, for example this XML has a different tags 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

Steps to follow

• Download and install Advanced ETL Processor [Link]
• Download and Unzip example [Link]
• Create a new transformation and open the .ats file

• Double click on the reader and select source XML file

• Repeat the process for the data writer (Point it at the result.csv file)

• Run the transformation by pressing the green arrow.

Press magnifying glass button to amend the XSLT transformation 

Please contact us if you need help with transforming the data