Skip to main content

Convert an XML file to CSV

Camel has built-in support for XSLT templates that can be used to convert XML into different formats. In the XSLT, you can define which nodes to pick from the XML tree and which structures to loop over to get the required data.

This article shows how to create an XSLT template and produce a CSV file.

Example XML-file to be converted to CSV

The file contains information about two transactions.

<feed>
    <entry>
        <content type="application/xml">
             <properties>
                <ID>12345678</ID>
                <CustomerID>123456</CustomerID>
                <AccountID>0123456789</AccountID>
                <CurrencyCode>SEK</CurrencyCode>
                <TransactionDate>2020-10-08</TransactionDate>
                <ValueDate>2020-10-12</ValueDate>
                <TransactionTypeText>Salg</TransactionTypeText>
                <ISIN>SE0014609087</ISIN>
                <InstrumentName>Sectra Redemption share</InstrumentName>
                <NetAmount>7355.03</NetAmount>
                <TransactionCurrencyPrice>71.161940</TransactionCurrencyPrice>
            </properties>
        </content>
    </entry>
    <entry>
        <content type="application/xml">
            <properties>
                <ID>87654321</ID>
                <CustomerID>654321</CustomerID>
                <AccountID>9876543210</AccountID>
                <CurrencyCode>SEK</CurrencyCode>
                <TransactionDate>2020-10-09</TransactionDate>
                <ValueDate>2020-10-13</ValueDate>
                <TransactionTypeText>Salg</TransactionTypeText>
                <ISIN>SE0014609087</ISIN>
                <InstrumentName>Sectra Redemption share</InstrumentName>
                <NetAmount>73.03</NetAmount>
                <TransactionCurrencyPrice>71.161940</TransactionCurrencyPrice>
            </properties>
        </content>
    </entry>
</feed>

Example XSLT used for converting XML to CSV

The file contains information on how the XML-file should be processed to CSV.

<?xml version="1.0"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="text" encoding="utf-8" /> 1 
   <xsl:template match="feed">2
       <xsl:text>ID;CustomerID;AccountID;CurrencyCode;TransactionDate;ValueDate;TransactionTypeText;ISIN;InstrumentName;NetAmount;TransactionCurrencyPrice&#xA;</xsl:text>3
        <xsl:for-each select="entry">4
            <xsl:value-of select="content/properties/ID" />5
            <xsl:text>;</xsl:text>6
            <xsl:value-of select="content/properties/CustomerID" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="content/properties/AccountID" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="content/properties/CurrencyCode" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="content/properties/TransactionDate" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="content/properties/ValueDate" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="content/properties/TransactionTypeText" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="content/properties/ISIN" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="content/properties/InstrumentName" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="content/properties/NetAmount" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="content/properties/TransactionCurrencyPrice" />
            <xsl:text>&#xA;</xsl:text>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

1

Define the xsl:output tag to output UTF-8 encoded text.

2

Define the feed tag.

3

Define the header rows with <xsl:text>header1;header2;...headerN;.

4

Pick up the transaction information from each entry with a for-each loop.

5

Select each value with a tag.

6

Add a separator between each row.

Using the XSLT template in a Camel route

Include the XSLT template in the Camel route by adding URI. Camel will process the XML and output CSV according to the rules defined in the XSLT.

Example shows the following URIs:

  • Path to the output XSLT file "ExampleXSLT.xslt".

  • Path to the output CSV.

<routes xmlns="http://camel.apache.org/schema/spring">   
    <route id="convertCustodyTransactionFile">
        <from uri="file:{{back.writable.dir}}/integrations/customFiles/xml?antInclude=*.xml&amp;noop=false&amp;readLock=changed&amp;readLockCheckInterval=5000&amp;moveFailed=.failed&amp;move=.camel" />
        <to uri="xslt:file:{{back.writable.dir}}/integrations/customFiles/xslt/ExampleXSLT.xslt"/> 
          <to uri="file:{{back.writable.dir}}/integrations/customFiles/csv?autoCreate=true&amp;fileName=${file:onlyname.noext}.csv&amp;charset=iso-8859-1"/>
    </route>
</routes>
In this section: