How can we convert XML file to CSV?

Question

I am having an XML file

<?xml version="1.0" encoding="ISO-8859-1"?>
<Results>
    <Row>
     <COL1></COL1>
     <COL2>25.00</COL2>
     <COL3>2009-07-06 15:49:34.984</COL3>
     <COL4>00001720</COL4>
    </Row>
    <Row>
     <COL1>RJ</COL1>
     <COL2>26.00</COL2>
     <COL3>2009-07-06 16:04:16.156</COL3>
     <COL4>00001729</COL4>
    </Row>
    <Row>
     <COL1>SD</COL1>
     <COL2>28.00</COL2>
     <COL3>2009-07-06 16:05:04.375</COL3>
     <COL4>00001721</COL4>
    </Row> 
</Results>

I have to convert this XML into CSV file. I have heard we can do such thing using XSLT. How can i do this in Java ( with/without XSLT )?

Answer 1

With XSLT you can use the JAXP interface to the XSLT processor and then use <xsl:text> in your stylesheet to convert to text output.

<xsl:text>&#10;</xsl:text>

generates a newline. for example.

Answer 2

Read the XML file in.

Loop throught each record and add it to a csv file.

Answer 3

In pseudo code:

loop through the rows:
    loop through all children of `Row`:
        write out the text
        append a comma
    new line

That quick little loop will write a comma at the end of each line, but I'm sure you can figure out how to remove that.

For actually parsing the XML, I suggest using JDOM. It has a pretty intuitive API.

Answer 4

Use the straightforward SAX API via the standard Java JAXP package. This will allow you to write a class that receives events for each XML element your reader encounters.

Briefly:

1. read your XML in using SAX
2. record text values via the SAX DefaultHandler characters() method
3. when you get an end event for a COL, record this string value
4. when you get the ROW end event, simply write out a comma separated line of previously recorded values

Answer 5

Using XSLT is often a bad idea. Use Apache Commons Digester. It's fairly easy to use - here's a rough idea::

Digester digester = new Digester();

digester.addObjectCreate("Results/Row", MyRowHolder.class);
digester.addCallMethod("Results/Row/COL1","addCol", 0);
// Similarly for COL2, etc.
digester.parse("mydata.xml");

This will create a MyRowHolder instance (where this is a class you provide). This class would have a addCol() method which would be called for each <COLn> with the contents of that tag.

Answer 6

In XSLT 1.0:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;

  <xsl:output method="text" encoding="ISO-8859-1" />

  <xsl:template match="/Results">
    <xsl:apply-templates select="Row" />  
  </xsl:template>

  <xsl:template match="Row">
    <xsl:apply-templates select="*" />  
    <xsl:if test="not(last())">
      <xsl:value-of select="'&#10;'" />  
    </xsl:if>
  </xsl:template>

  <xsl:template match="Row/*">
    <xsl:value-of select="." />
    <xsl:if test="not(last())">
      <xsl:value-of select="','" />  
    </xsl:if>
  </xsl:template>

</xsl:stylesheet>

If your COL* values can contain commas, you could wrap the values in double quotes:

  <xsl:template match="Row/*">
    <xsl:value-of select="concat('"', ., '"')" />
    <!-- ... --->

If they can contain commas and double quotes, things could get a bit more complex due to the required escaping. You know your data, you'll be able to decide how to best format the output. Using a different separator (e.g. TAB or a pipe symbol) is also an option.