XSLT - Nested XML to CSV output

Question

I am trying to convert a XML file to CSV using XSLT. I have the following (manually simplified version) of the XML file that I would like to convert. Each XML file can contain N number of records where N may vary.

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<report xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<reportheader>
<name>REPORT 1</name>
<version>v1</version>
</reportheader>
<recordtype>
    <account>
    <accountname>B</accountname>
    </account>
    <record>
    <time>12:00:00</time>
    <qty>10</qty>
    <price>20</price>
    </record>
    <record>
    <time>16:00:00</time>
    <qty>20</qty>
    <price>10</price>
    </record>   
</recordtype>
<recordtype>
    <account>
    <accountname>A</accountname>
    </account>
    <record>
    <time>16:00:00</time>
    <qty>5</qty>
    <price>10</price>
    </record>   
</recordtype>
</report>

The desired output would be the following:

name|version|accountname|time|qty|price
REPORT 1|v1|B|12:00:00|10|20
REPORT 1|v1|B|16:00:00|20|10
REPORT 1|v1|A|16:00:00|5|10

My current approach is to have two scripts. One XSLT that outputs all root nodes and the direct parent. In the second XSLT I then count the number of record nodes and loop through the csv column names N times. I then look for example to match /record/time[N]. The current approach borrows heavily from: https://pragmaticintegrator.wordpress.com/2012/10/28/transforming-xml-to-csv-via-xslt/ .

There are two problems with my current approach. 1, it assumes that every record always has every field available. 2. I cannot assign the right account to the records, because there might be only 6 accounts for 100 records. The way they are "loosely coupled" in the xml files makes my approach invalid.

Can someone help me find a more robust and efficient approach? Thanks in advance.

Caleth · Accepted Answer · 2018-07-23 12:36:33Z

1

You can do it in one go, by using the ancestor axis to look up the cousin values

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
  <xsl:template match="/">
    <xsl:text>name|version|accountname|time|qty|price&#xd;&#xa;</xsl:text>
    <xsl:apply-templates select="//record"/>
  </xsl:template>
  <xsl:template match="record">
    <xsl:value-of select="ancestor::report/reportheader/name"/>
    <xsl:text>|</xsl:text>
    <xsl:value-of select="ancestor::report/reportheader/version"/>
    <xsl:text>|</xsl:text>
    <xsl:value-of select="ancestor::recordtype/account/accountname"/>
    <xsl:text>|</xsl:text>
    <xsl:value-of select="time"/>
    <xsl:text>|</xsl:text>
    <xsl:value-of select="qty"/>
    <xsl:text>|</xsl:text>
    <xsl:value-of select="price"/>
    <xsl:text>&#xd;&#xa;</xsl:text>
  </xsl:template>
</xsl:stylesheet>

edited Jul 23, 2018 at 12:36

answered Jul 23, 2018 at 9:26

Caleth

66k2 gold badges53 silver badges101 bronze badges

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

3 Comments

GdvJ Over a year ago

Thanks, I can adapt this answer almost seamlessly to also fit the extended use case. If I may ask a small follow-up question, where do I insert a namespace 'x:' so that they work with the ancestor axis lookups?

Caleth Over a year ago

@GdvJ I think it's just ancestor::x:name

GdvJ Over a year ago

Right! I just had to remember to put 'x:' in front of every element in the tree and not just at the beginning.

imran · Accepted Answer · 2018-07-23 09:50:01Z

 <xsl:output method="text"/>
    <xsl:template match="report">
        <xsl:for-each select="reportheader">
            <xsl:value-of select="name/local-name(),version/local-name(), following-sibling::recordtype[1]/account/accountname/local-name(),following-sibling::recordtype[1]/record[1]/time/local-name(),following-sibling::recordtype[1]/record[1]/qty/local-name(),following-sibling::recordtype[1]/record[1]/price/local-name()" separator="|"/>
            </xsl:for-each>
        <xsl:for-each select="recordtype/record">
            <xsl:text>&#x0a;</xsl:text>
            <xsl:value-of select=" ancestor::report/reportheader/name,ancestor::report/reportheader/version, preceding-sibling::account/accountname,time,qty,price" separator="|"/>

        </xsl:for-each>
    </xsl:template>
You may also try it.

Collectives™ on Stack Overflow

XSLT - Nested XML to CSV output

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related