Using XSL transformation for XML to CSV conversion

Question

I have been working on the xml to csv transform and using Convert an XML file to CSV file using java method. However my .xsl file cannot product expected .csv result.

How can I proceed? How should I change my .xsl file?

here is the my .xml file:

<RowOfValues>
<RowValue>
<Value>XYZ</Value>
</RowValue>
<RowValue>
<Value>xyz1</Value>
</RowValue>
<RowValue>
<Value>xyz2</Value>
</RowValue>
<RowValue>
<Value>xyz3</Value>
</RowValue>
<RowValue>
<Value>xyz4</Value>
</RowValue>
</RowOfValues>
<RowOfValues>
<RowValue>
<Value>ABC</Value>
</RowValue>
<RowValue>
<Value>abc1</Value>
</RowValue>
<RowValue>
<Value>abc2</Value>
</RowValue>
<RowValue>
<Value>abc3</Value>
</RowValue>
<RowValue>
<Value>abc4</Value>
</RowValue>
</RowOfValues>

here is my .xsl file:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" >
    <xsl:output method="text" omit-xml-declaration="yes" indent="no"/>
    <xsl:template match="/">
        RowValue,Value
            <xsl:for-each select="//RowOfValues">
                <xsl:for-each select="//RowValue">
                    <xsl:value-of select="concat(Value,',','&#xA;')"/>
                </xsl:for-each>
            </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Current result(csv):

RowValue,Value

XYZ,
xyz1,
xyz2,
xyz3,
xyz4,
ABC,
abc1,
abc2,
abc3,
abc4,

Expected result(csv):

RowValue,Value
XYZ ABC
xyz1,abc1
xyz2,abc2
xyz3,abc3
xyz4,abc4

This is not a valid XML document, as it has no root element. — samjudson, Apr 24 '14 at 09:01
@samjudson you are right, but i dont appear all of xml file. — user3560030, Apr 24 '14 at 10:17

StuartLC · Accepted Answer · 2014-04-24T10:44:19.683

Assuming that there are only 2 columns (i.e. RowOfValues), and assuming both columns are of the same length:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                              xmlns:fo="http://www.w3.org/1999/XSL/Format" >
   <xsl:output method="text" omit-xml-declaration="yes" indent="no"/>
   <xsl:template match="/">
      <xsl:value-of select="concat('RowValue,Value', '&#xA;')"/>
      <xsl:for-each select="//RowOfValues[1]/RowValue">
         <xsl:variable name="pos" select="position()"/>
         <xsl:value-of select="concat(normalize-space(
                       concat(., ',', //RowOfValues[2]/RowValue[position()=$pos])),
                    '&#xA;')"/>
      </xsl:for-each>
   </xsl:template>
</xsl:stylesheet>

Edit

Here's one way to approach the N column case using a call template, although rather imperative :(

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
         xmlns:fo="http://www.w3.org/1999/XSL/Format" >
   <xsl:output method="text" omit-xml-declaration="yes" indent="no"/>
   <xsl:template match="/">
      <xsl:value-of select="concat('RowValue,Value', '&#xA;')"/>
      <xsl:for-each select="//RowOfValues[1]/RowValue">
         <xsl:variable name="pos" select="position()"/>
         <xsl:value-of select="normalize-space(.)"/>
         <xsl:call-template name="ScrapeColumns">
            <xsl:with-param name="pos" select="$pos"/>
         </xsl:call-template>
         <xsl:text>&#xA;</xsl:text>
      </xsl:for-each>
   </xsl:template>

   <xsl:template name="ScrapeColumns">
      <xsl:param name="pos"></xsl:param>
      <xsl:for-each select="//RowOfValues[position() > 1]//RowValue[position()=$pos]">
         <xsl:value-of select="concat(', ', normalize-space(.))"/>
      </xsl:for-each>   
   </xsl:template>
</xsl:stylesheet>

There are undoubtedly more elegant solutions.

many thanks, finally if there are more than 2 columns, how can it implement — user3560030, Apr 24 '14 at 10:19
I've updated with an N column version. You would probably want to adjust the column headers as well `RowValue,Value` — StuartLC, Apr 24 '14 at 10:45

score 0 · Answer 2 · answered Apr 24 '14 at 10:48

if there are more than 2 columns, how can it implement

Try it this way:

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

<xsl:key name="value-by-column" match="Value" use="count(../preceding-sibling::RowValue)" />

<xsl:template match="/">
    <xsl:for-each select="root/RowOfValues[1]/RowValue">
        <xsl:for-each select="key('value-by-column', count(preceding-sibling::RowValue))">
            <xsl:value-of select="."/>
            <xsl:if test="position()!=last()">
                <xsl:text>,</xsl:text>
            </xsl:if>
        </xsl:for-each>
        <xsl:if test="position()!=last()">
            <xsl:text>&#10;</xsl:text>
        </xsl:if>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

This will accommodate any number of columns you might have. Note that it is assumed that the number of values in each "column" (i.e. RowOfValues) is equal.

When applied to the following test document:

<root>
   <RowOfValues>
      <RowValue>
         <Value>Col A</Value>
      </RowValue>
      <RowValue>
         <Value>a 1</Value>
      </RowValue>
      <RowValue>
         <Value>a 2</Value>
      </RowValue>
   </RowOfValues>
   <RowOfValues>
      <RowValue>
         <Value>Col B</Value>
      </RowValue>
      <RowValue>
         <Value>b 1</Value>
      </RowValue>
      <RowValue>
         <Value>b 2</Value>
      </RowValue>
   </RowOfValues>
   <RowOfValues>
      <RowValue>
         <Value>Col C</Value>
      </RowValue>
      <RowValue>
         <Value>c 1</Value>
      </RowValue>
      <RowValue>
         <Value>c 2</Value>
      </RowValue>
   </RowOfValues>
</root>

the result is:

Col A,Col B,Col C
a 1,b 1,c 1
a 2,b 2,c 2

i got a blank csv output according to this solution because of some reason which is not appear in the eclipse. — user3560030, Apr 24 '14 at 11:13
@user3560030 You get a blank output using which input? There is a reason why I used my own test input: your input is invalid. — michael.hor257k, Apr 24 '14 at 11:15

Using XSL transformation for XML to CSV conversion

2 Answers2