How to convert XML to CSV using XSLT without knowing the node name

Question

I would like to parse and convert an XML file to CSV format using XSLT. The XML format looks like this:

            <a:level1>
              <a:level2>
                <b:level3">
                  <b:date a:value="TODAY">
                    <c:level4>
                      <d:level5>
                        <d:level6 a:value="AAA">
                          <d:level7 a:value="AAA_AAA">
                            <d:level8 a:value="XXX/123">
                              <d:leaf a:value="150415">
                                <b:leaf1>100</b:leaf1>
                                <b:leaf2>100</b:leaf2>
                              </d:leaf>
                              <d:leaf a:value="200814">
                                <b:leaf1>1961</b:leaf1>
                                <b:leaf2>1961</b:leaf2>
                              </d:leaf>
                            </d:level8>
                          </d:level7>
                        </d:level6>
                        <d:level6 a:value="BBB">
                          <d:level7 a:value="BBB_BBB">
                            <d:level8 a:value="XXX/123">
                              <d:leaf a:value="1505">
                                <b:leaf1>0.42</b:leaf1>
                                <b:leaf2>0.42</b:leaf2>
                              </d:leaf>
                            </d:level8>
                          </d:level7>
                        </d:level6>
                      </d:level5>
                    </c:level4>
                  </b:date>
                </b:level3>
              </a:level2>
            </a:level1>

The objective is to extract only nodes with values and use the node name as the header. The output CSV file will be like:

            date, level6, leve7, level8, leaf, leaf1, leaf2
            TODAY, AAA , AAA_AAA, XXX/123, 150415, 100,100
            TODAY, AAA , AAA_AAA, XXX/123, 200814, 1961,1961
            TODAY, BBB , BBB_BBB, XXX/123, 1505, 0.42,0.42

I am a newbie to XSLT, so do you have any samples on how construct the header and the rows in the CSV ? Node level6, level7, level8, leaf' names may change in different files.

Dimitre Novatchev Dimitre Novatchev · Accepted Answer · 2013-04-01T04:14:06

This transformation:

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

 <xsl:variable name="vFirstLeaf" select=
 "(//*[@a:value][not(descendant::*[@a:value])])[1]"/>

 <xsl:variable name="vAllLevels" select=
 "$vFirstLeaf/ancestor-or-self::*[@a:value]
 |
  $vFirstLeaf/*
 "/>

 <xsl:template match="/">
  <xsl:apply-templates select="$vAllLevels" mode="title"/>
  <xsl:text>&#xA;</xsl:text>
  <xsl:apply-templates mode="lastNormal" select=
  "//*[@a:value and not(descendant::*[@a:value])]"/>
 </xsl:template>

 <xsl:template match="*" mode="title">
  <xsl:if test="not(position()=1)">, </xsl:if>
  <xsl:value-of select="local-name()"/>
 </xsl:template>

 <xsl:template match="*" mode="lastNormal">
  <xsl:apply-templates mode="value"
    select="ancestor-or-self::*[@a:value]"/>
  <xsl:apply-templates select="*"/>
  <xsl:text>&#xA;</xsl:text>
 </xsl:template>

 <xsl:template match="*" mode="value">
  <xsl:if test="not(position()=1)">, </xsl:if>
  <xsl:value-of select="@a:value"/>
 </xsl:template>

 <xsl:template match="*">
  <xsl:text>, </xsl:text>
  <xsl:value-of select="."/>
 </xsl:template>
</xsl:stylesheet>

when applied on the provided XML document:

<a:level1 xmlns:a="some:a" xmlns:b="some:b"
          xmlns:c="some:c" xmlns:d="some:d">
    <a:level2>
        <b:level3>
            <b:date a:value="TODAY">
                <c:level4>
                    <d:level5>
                        <d:level6 a:value="AAA">
                            <d:level7 a:value="AAA_AAA">
                                <d:level8 a:value="XXX/123">
                                    <d:leaf a:value="150415">
                                        <b:leaf1>100</b:leaf1>
                                        <b:leaf2>100</b:leaf2>
                                    </d:leaf>
                                    <d:leaf a:value="200814">
                                        <b:leaf1>1961</b:leaf1>
                                        <b:leaf2>1961</b:leaf2>
                                    </d:leaf>
                                </d:level8>
                            </d:level7>
                        </d:level6>
                        <d:level6 a:value="BBB">
                            <d:level7 a:value="BBB_BBB">
                                <d:level8 a:value="XXX/123">
                                    <d:leaf a:value="1505">
                                        <b:leaf1>0.42</b:leaf1>
                                        <b:leaf2>0.42</b:leaf2>
                                    </d:leaf>
                                </d:level8>
                            </d:level7>
                        </d:level6>
                    </d:level5>
                </c:level4>
            </b:date>
        </b:level3>
    </a:level2>
</a:level1>

produces the wanted, correct result:

date, level6, level7, level8, leaf, leaf1, leaf2
TODAY, AAA, AAA_AAA, XXX/123, 150415, 100, 100
TODAY, AAA, AAA_AAA, XXX/123, 200814, 1961, 1961
TODAY, BBB, BBB_BBB, XXX/123, 1505, 0.42, 0.42

How to convert XML to CSV using XSLT without knowing the node name

1 Answers