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
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
Visit ETL Tools Forum |