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

Will Furniture nodes be variable across rooms with no min or max limits? — Parfait, Dec 13 '16 at 02:47
yes. but each room will only have 1 data, which is a sum of the price for each furniture..i.e a room has 4 furniture, the output in the csv will be the total amount of that 4 furniture.. — Nico, Dec 13 '16 at 02:50
Your bottom result sum is *even* easier than your original. What is the comma-separated values at top? Wait...why is `` summing to 40000 and not 30000, next 70000, next 110000? — Parfait, Dec 13 '16 at 03:08
@Parfait `sum(@FURN_AMT * @price)`, i.e. `a = 2 * 10000 + 1 * 20000 = 40000`. — Andreas, Dec 13 '16 at 04:12
@NicoPratama How come you are hard-coding the header? Does every house have the same number of rooms? — michael.hor257k, Dec 13 '16 at 06:44
I have rolled your question back to what it was when it was answered. Please post your new question separately. — michael.hor257k, Dec 14 '16 at 07:40

score 2 · Answer 1 · answered Dec 13 '16 at 02:09

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>

answered Dec 13 '16 at 02:09

Andreas

154,647
11
152
247

if furniture f has more than 2 furniture, how can I get the total price of all furniture in room f?just realized the output xml only have 2 furniture for each room, if there's more than 2 furniture in one room it wouldn't be included in the loop, see edited – Nico Dec 13 '16 at 02:57
1

What output do you expect now? 3 individual lines with line 3 having blanks fields? Or a single summary line? The summary line is much simpler, so if that's what you want, why go for the intermediate lines that doesn't have numeric values? Please edit question again and **clarify** what you want. – Andreas Dec 13 '16 at 04:14
hi, updated the question (see the last part, expected final output) – Nico Dec 13 '16 at 07:06
1

Now, that's a *totally* different result from the original question. Thanks for wasting our time trying to help you. Next time, please figure out what you want *before* asking a question. For the updated question, see [how to apply group by on xslt elements](http://stackoverflow.com/q/2146648/5221149). – Andreas Dec 13 '16 at 15:16
i just want to figure out what I'm stuck at the moment, so I ask a simpler question first..the xml/output is not the one I'm working at, because it's confidential..I create a sample (rewrite the code) with this house-furniture relationship..However, your answer helps a lot though.. I'm stuck at figuring out how to get the final output, I'll look at your referenced link in a sec. – Nico Dec 14 '16 at 02:03
1

@Nico The problem with asking a simpler question first, is that it may be going down the wrong path. This is called the [XY Problem](http://meta.stackexchange.com/a/66378). Let me quote the beginning from that link: *The XY problem is asking about your attempted solution rather than your actual problem. [...] This can lead to **frustration by people [like me]** who are trying to help you solve the problem because by the time you ask about it, the solution that you need help with might not have any obvious connections to the problem that you are trying to solve.* – Andreas Dec 14 '16 at 04:49
so sorry for the frustration and will try to cop in stackoverflow better next time... – Nico Dec 14 '16 at 06:15

michael.hor257k · Accepted Answer · 2016-12-13T06:48:37.897

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?).

i'm editing the question based on @Andreas 's suggestion.. the final output is updated on my post.. — Nico, Dec 13 '16 at 07:05
@NicoPratama The more you clarify, the less clear it gets. It seems to me you should figure out what you need first, before you ask others to take time to help. — michael.hor257k, Dec 13 '16 at 07:10
in short explanation, I need the house name and furniture id as the primary key for the csv..so each furniture for each house will has its own row by itself..i.e house01 + furniture01, house01 + furniture02, house01 + furniture03, house02 + furniture01, and so on.. — Nico, Dec 13 '16 at 07:27
@NicoPratama That would be a trivial problem - where are you stuck with it? — michael.hor257k, Dec 13 '16 at 07:30
@michael.hor275k updated xsl that i'm working, do i need xsl grouping to reach the final output that I want (edited)? — Nico, Dec 14 '16 at 02:05
@Nico Please ask this as a new question. Your original question already has two answers, you should not change it to something so different. — michael.hor257k, Dec 14 '16 at 07:38
@michael.hor275k will do. thanks, the link is [here](http://stackoverflow.com/questions/41139314/xml-convertion-to-csv) — Nico, Dec 14 '16 at 09:43

Converting unusual XML data to CSV through XSLT

2 Answers2