Transforming XML to CSV via XSLT

As the archive of this blog might make clear I am a big fan of the Altova product MapForce. I have used this tool a lot for transforming XML to XML or CSV. However, in my current project I also had to make a XML to CSV transformation but it was such a simple case I decided to stick to XSLT for the transformation.

The XML and CSV that are involved in this are the following:

<?xml version="1.0"?>
<importFile template="default" dateFmt="DD/MM/YYYY">
<priceImport type="DAILY" >
<market>GAS</market>
<comp>ZEE</comp>
<date>24/10/2012</date>
<maturity>25/10/2012</maturity>
<settle>F</settle>
<pformat>*</pformat>
<pset>MARKET</pset>
<price>0,75</price>
</priceImport>
<priceImport type = "DAILY" >
<market>GAS</market>
<comp>ZEE</comp>
<date>24/10/2012</date>
<maturity>26/10/2012</maturity>
<settle>F</settle>
<pformat>*</pformat>
<pset>MARKET</pset>
<price>0,78</price>
</priceImport>
...
</importFile>

This had to be transformed to

IMPORT TYPE;TYPE;MARKET;COMP;DATE;MATURITY;SETTLE;PFORMAT;PSET;PRICE
PRICE;DAILY;GAS;ZEE;24/10/2012;25/10/2012;F;*;MARKET;0,75
PRICE;DAILY;GAS;ZEE;24/10/2012;26/10/2012;F;*;MARKET;0,78
...

The observant reader may recognize the XML and CSV format which is based on the Sungard Aligne formats

To go short, to perform the necessary translation I used the following XSLT:

<?xml version="1.0"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:csv="csv:csv">
	<xsl:output method="text" encoding="utf-8"/>
	<xsl:strip-space elements="*"/>
	<xsl:variable name="delimiter" select="';'"/>
	<csv:columns>
		<column>import type</column>
		<column>type</column>
		<column>market</column>
		<column>comp</column>
		<column>date</column>
		<column>maturity</column>
		<column>settle</column>
		<column>pformat</column>
		<column>pset</column>
		<column>price</column>
	</csv:columns>
	<xsl:template match="/importFile">
		<!-- Output the CSV header -->
		<xsl:for-each select="document('')/*/csv:columns/*">
			<xsl:value-of select="upper-case(.)"/>
			<xsl:if test="position() != last()">
				<xsl:value-of select="$delimiter"/>
			</xsl:if>
		</xsl:for-each>
		<xsl:text>
</xsl:text>
		<!-- Output rows for each matched property -->
		<xsl:apply-templates select="*"/>
	</xsl:template>
	<xsl:template match="/importFile/priceImport">
		<xsl:variable name="property" select="."/>
		<!-- Loop through the columns in order -->
		<xsl:for-each select="document('')/*/csv:columns/*">
			<!-- Extract the column name and value -->
			<xsl:variable name="column" select="."/>
			<xsl:variable name="value" select="$property/*[name() = $column]"/>
			<xsl:value-of select="$value"/>
			<xsl:if test="$column = 'type'">
				<xsl:value-of select="$property/@type"/>
			</xsl:if>
			<xsl:if test="$column = 'import type'">PRICE</xsl:if>
			<!-- Add the delimiter unless we are the last expression -->
			<xsl:if test="position() != last()">
				<xsl:value-of select="$delimiter"/>
			</xsl:if>
		</xsl:for-each>
		<!-- Add a newline at the end of the record -->
		<xsl:text>
</xsl:text>
	</xsl:template>
</xsl:stylesheet>

I think the XSLT is quite straightforward. I create a list of column names and use that list to parse the XML elements. In case of the column ‘TYPE’ I take the value of the attribute ‘type’ and in case of the column ‘import type’ I use the hardcoded value ‘PRICE’. In all other cases I select the value from the element that corresponds with the column name.

Advertisement