A CSV to XML converter in XSLT 2.0

* Note: This transform has been updated http://andrewjwelch.com/code/xslt/csv/csv-to-xml_v2.html

I wrote a rudimentary csv to XML converter a while back which broke when the csv contained quoted values, eg foo, "foo, bar", bar Dealing with these quotes is surprisingly hard, especially when you take into account quotes are escaped by doubling them.

I raised it on xsl-list, and Abel Braaksma came up with a genious solution - the technique is to use both sides of analyze-string - read his post for the best explanation.

To keep the transform generic I've used an attribute instead of an element for the column names to cope with names that aren't valid QNames (for example ones that contain a space) - for my own use would add a function to convert names to valid QNames and then change <elem name="{...}"> to <xsl:element name="{fn:getQName(...)}"> as it generates nicer XML. I've also modified the non-matching-substring side of analyze-string to only return tokens that contain values.

So this sample input:

Col 1, Col 2, Col 3
foo, "foo,bar", "foo:""bar"""

...creates this output:

<root>
<row>
  <elem name="Col 1">foo</elem>
  <elem name="Col 2">foo,bar</elem>
  <elem name="Col 3">foo:"bar"</elem>
</row>
</root>

Here's the finished transform:

<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:fn="fn"
exclude-result-prefixes="xs fn">

<xsl:output indent="yes" encoding="US-ASCII"/>

<xsl:param name="pathToCSV" select="'file:///c:/csv.csv'"/>

<xsl:function name="fn:getTokens" as="xs:string+">
<xsl:param name="str" as="xs:string"/>
<xsl:analyze-string regex='("[^"]*")+' select="$str">
   <xsl:matching-substring>
       <xsl:sequence select='replace(., "^""|""$|("")""", "$1")'/>
   </xsl:matching-substring>
   <xsl:non-matching-substring>
       <xsl:for-each select="tokenize(., '\s*,\s*')">
           <xsl:sequence select="."/>
       </xsl:for-each>
   </xsl:non-matching-substring>
</xsl:analyze-string>
</xsl:function>

<xsl:template match="/" name="main">
<xsl:choose>
    <xsl:when test="unparsed-text-available($pathToCSV)">
        <xsl:variable name="csv" select="unparsed-text($pathToCSV)"/>
        <xsl:variable name="lines" select="tokenize($csv, '&#xa;')" as="xs:string+"/>
        <xsl:variable name="elemNames" select="fn:getTokens($lines[1])" as="xs:string+"/>
        <root>
            <xsl:for-each select="$lines[position() > 1]">
                <row>
                    <xsl:variable name="lineItems" select="fn:getTokens(.)" as="xs:string+"/>

                    <xsl:for-each select="$elemNames">
                        <xsl:variable name="pos" select="position()"/>
                        <elem name="{.}">
                            <xsl:value-of select="$lineItems[$pos]"/>
                        </elem>
                    </xsl:for-each>
                </row>
            </xsl:for-each>
        </root>
    </xsl:when>
    <xsl:otherwise>
        <xsl:text>Cannot locate : </xsl:text>
        <xsl:value-of select="$pathToCSV"/>
    </xsl:otherwise>
</xsl:choose>
</xsl:template>

</xsl:stylesheet>