Transforming XML to CSV - details

Question

Hi all,

I have some trouble with the correct formulation of a transform. I'm generating CSV files. I can easily generate the following csv:

   "version","","stuff",
    "version1version2","annotation1annotation2","yadda",

However, I would like for the different instances of subfields to be comma-separated within their string, as follows:

"version","","stuff",
"version1,version2","annotation1,annotation2","yadda",

My input looks something like

<?xml version="1.0" encoding="UTF-8"?>
<collection>
  <record>
    <datafield tag="020">
      <subfield code="a">version</subfield>
    </datafield>
    <datafield tag="040">
      <subfield code="b">stuff</subfield>
    </datafield>
  </record>
  <record>
    <datafield tag="020">
      <subfield code="a">version1</subfield>
      <subfield code="9">annotation1</subfield>
    </datafield>
    <datafield tag="020">
      <subfield code="a">version2</subfield>
      <subfield code="9">annotation2</subfield>
    </datafield>
    <datafield tag="040">
      <subfield code="b">yadda</subfield>
    </datafield>
  </record>
</collection>

Using the following xsl (and xsltproc)

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="text"/>

<xsl:template match="/">
  <xsl:apply-templates select="collection/record"/>
</xsl:template>

<xsl:template match="record">
  <xsl:text>"</xsl:text>
  <xsl:apply-templates select="datafield[@tag='020']/subfield[@code='a']"/>
  <xsl:text>",</xsl:text>

  <xsl:text>"</xsl:text>
  <xsl:apply-templates select="datafield[@tag='020']/subfield[@code='9']"/>
  <xsl:text>",</xsl:text>

  <xsl:text>"</xsl:text>
  <xsl:apply-templates select="datafield[@tag='040']/subfield[@code='b']"/>
  <xsl:text>",</xsl:text>

  <xsl:text>
</xsl:text>
</xsl:template>

I would guess that some combination of following-sibling:: or not(position()=last()) with call-template is going to be involved, but I haven't hit on a working solution yet. Any help?

I'm not looking for a generic XML-to-csv transform - anything geared to this particular dataset is fine.

Answer 1

<xsl:template match="record">
  <xsl:text>"</xsl:text>
  <xsl:apply-templates select="datafield[@tag='020']/subfield[@code='a']" />
  <xsl:text>",</xsl:text>

  <xsl:text>"</xsl:text>
  <xsl:apply-templates select="datafield[@tag='020']/subfield[@code='9']" />
  <xsl:text>",</xsl:text>

  <xsl:text>"</xsl:text>
  <xsl:apply-templates select="datafield[@tag='040']/subfield[@code='b']" />
  <xsl:text>",</xsl:text>

  <xsl:text>&#xa;</xsl:text><!-- line break -->
</xsl:template>

<xsl:template match="subfield">
  <xsl:value-of select="." />
  <xsl:if test="position() != last()">,</xsl:if>
</xsl:template>

For larger input data sets, introducing a key will result in better overall performance:

<xsl:key name="kSubfield" match="datafield/subfield" use="
  concat(
    generate-id(ancestor::record), '|', parent::datafield/@tag, '|',  @code
  )
" />

<!-- ... -->

<xsl:template match="record">
  <xsl:variable name="recordId" select="generate-id()" />

  <xsl:text>"</xsl:text>
  <xsl:apply-templates select="key('kSubfield', concat($recordId, '|020|a'))" />
  <xsl:text>",</xsl:text>

  <xsl:text>"</xsl:text>
  <xsl:apply-templates select="key('kSubfield', concat($recordId, '|020|9'))" />
  <xsl:text>",</xsl:text>

  <xsl:text>"</xsl:text>
  <xsl:apply-templates select="key('kSubfield', concat($recordId, '|040|b'))" />
  <xsl:text>",</xsl:text>

  <xsl:text>&#xa;</xsl:text><!-- line break -->
</xsl:template>

Answer 2

In more general way, this stylesheet:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
    <xsl:output method="text"/>
    <xsl:key name="field" match="subfield" use="concat(../@tag,'&#xA;',@code)"/>
    <xsl:variable name="fields" select="/*/*/*/subfield[count(.|key('field',concat(../@tag,'&#xA;',@code))[1])=1]"/>
    <xsl:template match="record">
        <xsl:variable name="me" select="."/>
        <xsl:for-each select="$fields">
            <xsl:text>"</xsl:text>
            <xsl:apply-templates select="$me/*/*[concat(../@tag,'&#xA;',@code)=concat(current()/../@tag,'&#xA;',current()/@code)]"/>
            <xsl:text>"</xsl:text>
            <xsl:if test="position() != last()">,</xsl:if>
        </xsl:for-each>
        <xsl:text>&#xA;</xsl:text>
    </xsl:template>
    <xsl:template match="subfield">
        <xsl:value-of select="."/>
        <xsl:if test="position() != last()">,</xsl:if>
    </xsl:template>
</xsl:stylesheet>

Result:

"version","stuff",""
"version1,version2","yadda","annotation1,annotation2"