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
– Parfait, Commented Dec 13, 2016 at 2: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
– Nico, Commented Dec 13, 2016 at 2:50
Your bottom result sum is even easier than your original. What is the comma-separated values at top? Wait...why is <a> summing to 40000 and not 30000, next 70000, next 110000? — Parfait
– Parfait, Commented Dec 13, 2016 at 3:08
@Parfait sum(@FURN_AMT * @price), i.e. a = 2 * 10000 + 1 * 20000 = 40000. — Andreas
– Andreas, Commented Dec 13, 2016 at 4:12

Andreas · Accepted Answer · 2016-12-13 02:09:43Z

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, 2016 at 2:09

Andreas

160k13 gold badges164 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Nico Over a year ago

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

Andreas Over a year ago

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.

Nico Over a year ago

hi, updated the question (see the last part, expected final output)

Andreas Over a year ago

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.

Andreas Over a year ago

@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. 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.

|

michael.hor257k · Accepted Answer · 2016-12-13 06:48:37Z

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

edited Dec 13, 2016 at 6:48

answered Dec 13, 2016 at 6:42

michael.hor257k

118k6 gold badges36 silver badges55 bronze badges

7 Comments

Nico Over a year ago

i'm editing the question based on @Andreas 's suggestion.. the final output is updated on my post..

michael.hor257k Over a year ago

@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.

Nico Over a year ago

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

michael.hor257k Over a year ago

@NicoPratama That would be a trivial problem - where are you stuck with it?

Nico Over a year ago

@michael.hor275k updated xsl that i'm working, do i need xsl grouping to reach the final output that I want (edited)?

|

Collectives™ on Stack Overflow

Converting unusual XML data to CSV through XSLT

2 Answers 2

7 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related