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.

About Pascal Alma

Pascal is a senior IT consultant and has been working in IT since 1997. He is monitoring the latest development in new technologies (Mobile, Cloud, Big Data) closely and particularly interested in Java open source tool stacks, cloud related technologies like AWS and mobile development like building iOS apps with Swift. Specialties: Java/JEE/Spring Amazon AWS API/REST Big Data Continuous Delivery Swift/iOS
This entry was posted in XML/ XSD/ XSLT and tagged . Bookmark the permalink.