Converting unusual XML data to CSV through XSLT

Question

<?xml version="1.0" encoding="UTF-8"?>
<FirstTag version="1.0" createTime="15:59:59" DATE="20161209">
  <SecondTag Name="House01">
    <a>
        <Furniture FURN_ID="FUR00001" FURN_AMT="2" price="10000"/>
        <Furniture FURN_ID="FUR00002" FURN_AMT="1" price="20000"/>
    </a>
    <b>
        <Furniture FURN_ID="FUR00003" FURN_AMT="2" price="30000"/>
        <Furniture FURN_ID="FUR00004" FURN_AMT="1" price="40000"/>
    </b>
    <c>
        <Furniture FURN_ID="FUR00005" FURN_AMT="2" price="50000"/>
        <Furniture FURN_ID="FUR00006" FURN_AMT="1" price="60000"/>
    </c>
    <d>
        <Furniture FURN_ID="FUR00007" FURN_AMT="1" price="70000"/>
        <Furniture FURN_ID="FUR00008" FURN_AMT="1" price="80000"/>
    </d>
    <e>
        <Furniture FURN_ID="FUR00009" FURN_AMT="1" price="90000"/>
        <Furniture FURN_ID="FUR00010" FURN_AMT="1" price="100000"/>
    </e>
    <f>
        <Furniture FURN_ID="FUR00011" FURN_AMT="1" price="110000"/>
        <Furniture FURN_ID="FUR00012" FURN_AMT="2" price="120000"/>
        <Furniture FURN_ID="FUR00013" FURN_AMT="2" price="120000"/>
    </f>
  </SecondTag>
</FirstTag>

Above is the simple xml (with node value), that I produced from my Java program. The point is, I want to send this xml data to another application, where there's already a csv load function from the UI/batch processes. I've heard of XSLT but never use of it, tried some of the tutorial but got confused in the time to get all the values into a csv.

Here's what it should look like in csv (to start, after success need to do some calculation):

In this example in one house (HOUSE01) I would like to output all the furniture in different room (i.e. a is room 1, b is room 2, c is room 3, etc).

I've been trying to build the XSLT, below is the XSLT:

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

  <xsl:variable name="delimiter" select="','" />

  <!-- define an array containing the fields we are interested in -->
  <xsl:variable name="fieldArray">
    <field>Name</field>
    <field>a</field>
    <field>b</field>
    <field>c</field>
    <field>d</field>
    <field>e</field>
    <field>f</field>
  </xsl:variable>
  <xsl:param name="fields" select="document('')/*/xsl:variable[@name='fieldArray']/*" />

  <xsl:template match="/">

    <!-- output the header row -->
    <xsl:for-each select="$fields">
      <xsl:if test="position() != 1">
        <xsl:value-of select="$delimiter"/>
      </xsl:if>
      <xsl:value-of select="." />
    </xsl:for-each>

    <!-- output newline -->
    <xsl:text>
</xsl:text>

    <xsl:apply-templates select="/*/*"/>
  </xsl:template>

  <xsl:template match="a">
    <xsl:variable name="currNode" select="." />

<!--     output the data row -->
<!--     loop over the field names and find the value of each one in the xml -->
    <xsl:for-each select="$fields">
      <xsl:if test="position() != 1">
        <xsl:value-of select="$delimiter"/>
      </xsl:if>
      <xsl:value-of select="$currNode/*[name() = current()]/@FURN_ID" />
<!--       <xsl:value-of select="$currNode/*[name() = current()]" /> -->
    </xsl:for-each>

<!--     output newline -->
    <xsl:text>
</xsl:text>
  </xsl:template>
</xsl:stylesheet>

I'm using some reference from another page, and can build some simple XSLT to transform XML to CSV, however, I need some guidance in order to solve my main XML issue. In the future after I can get the node value inside the loop, I'd like to sum the total price of every furniture for each room.

Expected final csv result:

Name,a,b,c,d,e,f
House01,40000,100000,160000,150000,190000,350000

Thank you.

Getting the value of an attribute in XML

Answer 1

The second (final) .csv can be produced as follows:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:exsl="http://exslt.org/common"
extension-element-prefixes="exsl">
<xsl:output method="text" encoding="UTF-8" />

<xsl:template match="/FirstTag">
    <!-- first pass -->
    <xsl:variable name="values-rtf">
        <xsl:for-each select="SecondTag/*">
            <xsl:copy>
                <xsl:for-each select="Furniture">
                    <value>
                        <xsl:value-of select="@FURN_AMT * @price"/>
                    </value>
                </xsl:for-each>
            </xsl:copy>
        </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="values" select="exsl:node-set($values-rtf)/*" />
    <!-- header -->
    <xsl:text>Name,</xsl:text>
    <xsl:for-each select="$values">
        <xsl:value-of select="name()"/>
            <xsl:if test="position()!=last()">
                <xsl:text>,</xsl:text>
            </xsl:if>
    </xsl:for-each>
    <xsl:text>&#10;</xsl:text>
    <!-- summary -->
    <xsl:value-of select="SecondTag/@Name"/>
    <xsl:text>,</xsl:text>
    <xsl:for-each select="$values">
        <xsl:value-of select="sum(value)"/>
            <xsl:if test="position()!=last()">
                <xsl:text>,</xsl:text>
            </xsl:if>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

This assumes you are limited to XSLT 1.0; in XSLT 2.0 this could be done in a single pass.

Note that I am assuming the input XML will contain a single "house" (SecondTag), with a variable number of "rooms" (a, b, c, etc.). Otherwise it's not clear what the header of the .csv should be.

---

I am not sure if you need to also have the interim .csv - and in any case, the logic required to create it is not clear (why is FUR00013 missing from the output?).

Answer 2

This XSLT will give the output you specified. See demo.

Updated: I missed the a value in the output.

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

  <xsl:template match="/">
    <xsl:text>Name,a,b,c,d,e,f
</xsl:text>
    <xsl:apply-templates select="FirstTag/SecondTag/a/Furniture"/>
  </xsl:template>

  <xsl:template match="Furniture">
    <xsl:variable name="pos" select="position()"/>
    <xsl:value-of select="../../@Name"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="@FURN_ID"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="../../b/Furniture[position()=$pos]/@FURN_ID"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="../../c/Furniture[position()=$pos]/@FURN_ID"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="../../d/Furniture[position()=$pos]/@FURN_ID"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="../../e/Furniture[position()=$pos]/@FURN_ID"/>
    <xsl:text>,</xsl:text>
    <xsl:value-of select="../../f/Furniture[position()=$pos]/@FURN_ID"/>
    <xsl:text>
</xsl:text>
  </xsl:template>

</xsl:stylesheet>