XML Convertion to CSV

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="FUR00001" FURN_AMT="2" price="30000"/>
        <Furniture FURN_ID="FUR00003" FURN_AMT="1" price="40000"/>
    </b>
    <c>
        <Furniture FURN_ID="FUR00002" FURN_AMT="2" price="50000"/>
        <Furniture FURN_ID="FUR00003" FURN_AMT="1" price="60000"/>
    </c>
    <d>
        <Furniture FURN_ID="FUR00001" FURN_AMT="1" price="70000"/>
        <Furniture FURN_ID="FUR00002" FURN_AMT="2" price="80000"/>
    </d>
    <e>
        <Furniture FURN_ID="FUR00002" FURN_AMT="1" price="90000"/>
        <Furniture FURN_ID="FUR00003" FURN_AMT="2" price="100000"/>
    </e>
    <f>
        <Furniture FURN_ID="FUR00001" FURN_AMT="1" price="110000"/>
        <Furniture FURN_ID="FUR00002" FURN_AMT="2" price="120000"/>
        <Furniture FURN_ID="FUR00003" 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:template match="/">
    <xsl:text>Name,Furn_Id,a,b,c,d,e,f
</xsl:text>
  <xsl:variable name="delimiter" select="','" />

    <xsl:for-each select="//SecondTag">
<xsl:variable name="hname" select="@Name"/>

        <xsl:for-each select="f/Furniture">

            <xsl:value-of select='$hname' />
            <xsl:value-of select="$delimiter"/>

            <xsl:variable name="var1" select="@FURN_ID"/>
            <xsl:variable name="varamt1" select="@FURN_AMT"/>

             <xsl:for-each select="//a/Furniture">


                 <xsl:variable name="var2" select="@FURN_ID"/>
                 <xsl:variable name="varamt2" select="@FURN_AMT"/>

                <xsl:if test="$var1 = $var2">

                        <xsl:value-of select='$var2' />
                        <xsl:value-of select="$delimiter"/>
                        <xsl:value-of select='$varamt2' />
                        <xsl:value-of select="$delimiter"/>
                </xsl:if>

            </xsl:for-each>

<xsl:text>
</xsl:text>

        </xsl:for-each>

    </xsl:for-each>
</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,Furn_Id,a,b,c,d,e,f
House01,FUR00001,20000,60000,,70000,,110000
House01,FUR00002,20000,,100000,160000,90000,240000
House01,FUR00003,,40000,60000,,200000,240000

Thank you.

Getting the value of an attribute in XML

Answer 1

This is more complicated than it may seem, because you want a row for each distinct FURN_ID in the current house (or at least so it seems from the given example). So the first task is to find these distinct FURN_ID values.

OTOH, if each house has exactly the same 6 rooms, then the second task of showing each furniture under its matching column header is much simpler:

XSLT 1.0

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

<xsl:key name="furn" match="Furniture" use="concat(@FURN_ID, '|', ../../@Name)" />

<xsl:template match="/FirstTag">
    <xsl:for-each select="SecondTag">
        <xsl:variable name="columns" select="*" />
        <xsl:variable name="unique-rows" select="*/Furniture[count(. | key('furn', concat(@FURN_ID, '|', ../../@Name))[1]) = 1]"/>
        <!-- header -->
        <xsl:text>Name,Furn_Id,a,b,c,d,e,f&#10;</xsl:text>
        <!-- rows -->
        <xsl:for-each select="$unique-rows">
            <xsl:value-of select="../../@Name"/>
            <xsl:text>,</xsl:text>
            <xsl:value-of select="@FURN_ID"/>
            <xsl:text>,</xsl:text>
            <xsl:variable name="id" select="@FURN_ID" />
            <xsl:for-each select="$columns">
                <xsl:variable name="cell" select="Furniture[@FURN_ID=$id]" />
                <xsl:if test="$cell">
                    <xsl:value-of select="$cell/@FURN_AMT * $cell/@price"/>
                </xsl:if>
                <xsl:if test="position()!=last()">
                    <xsl:text>,</xsl:text>
                </xsl:if>
            </xsl:for-each>
            <xsl:text>&#10;</xsl:text>
        </xsl:for-each>
     </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Applied to your input example, the result will be:

Name,Furn_Id,a,b,c,d,e,f
House01,FUR00001,20000,60000,,70000,,110000
House01,FUR00002,20000,,100000,160000,90000,240000
House01,FUR00003,,40000,60000,,200000,240000

which is slightly different from your expected output, but I believe it is the correct one.