Generate CSV output using XSL

Question

I have a xml file in this structure (see below) and I need to generate csv output from the same.

<Root>
  <Metadata>
   <id>A001</id>
   <name>Test</name>
  </Metadata>
  <Employers>
    <Employer id="111">
      <Employee id="aaa"><Name>Rick</Name></Employee>
      <Employee id="bbb"><Name>Ram</Name></Employee>
    </Employer>
    <Employer id="222">
      <Employee id="ddd"><Name>Bob</Name></Employee>
      <Employee id="dcc"><Name>Tan</Name></Employee>
    </Employer>
  </Employers>
</Root>

Using xsl I need to generate a csv output like the below:

A001, Test, 111, aaa, Rick
A001, Test, 111, bbb, Ram
A001, Test, 222, ddd, Bob
A001, Test, 222, dcc, Tan

Can anyone please tell me how to generate this? FYI, I am able to generate the Employer data elements but unable to generate Metadata elements for each & every Employer row.

Do you expect to follow Employers with another Metadata and yet more Employers? — G. Ken Holman
– G. Ken Holman, Commented Sep 26, 2013 at 23:09
I need Metadata elements first (i.e. for each row) and then the Employer details. — user972255
– user972255, Commented Sep 26, 2013 at 23:44
I understand that. My question is will you have two or more Metadata elements in your input XML, with separate Employers after each one? And do you need XSLT 1.0 or XSLT 2.0? — G. Ken Holman
– G. Ken Holman, Commented Sep 26, 2013 at 23:46

G. Ken Holman · Accepted Answer · 2013-09-27 01:41:28Z

Here is a transcript of a solution for you that follows RFC4180. The extra space you have after a comma should not be there.

Data:

T:\ftemp>type emp2csv.xml 
<Root>
  <Metadata>
   <id>A001</id>
   <name>Test</name>
  </Metadata>
  <Employers>
    <Employer id="111">
      <Employee id="aaa"><Name>Rick</Name></Employee>
      <Employee id="bbb"><Name>Ram</Name></Employee>
    </Employer>
    <Employer id="222">
      <Employee id="ddd"><Name>Bob</Name></Employee>
      <Employee id="dcc"><Name>Tan</Name></Employee>
    </Employer>
  </Employers>
</Root>

Execution:

T:\ftemp>call xslt emp2csv.xml emp2csv.xsl 
A001,Test,111,aaa,Rick
A001,Test,111,bbb,Ram
A001,Test,222,ddd,Bob
A001,Test,222,dcc,Tan

Stylesheet:

T:\ftemp>type emp2csv.xsl 
<?xml version="1.0" encoding="US-ASCII"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                version="1.0">

<xsl:output method="text"/>

<xsl:variable name="commonFields"
              select="/*/Metadata/id | /*/Metadata/name"/>

<xsl:template match="/">
  <xsl:apply-templates select="Root/Employers/Employer/Employee"/>
</xsl:template>

<!--these elements are CSV fields-->
<xsl:template match="Employee">
  <xsl:for-each select="$commonFields | ../@id | @id | Name">
    <xsl:call-template name="doThisField"/>
    <xsl:if test="position() != last()">,</xsl:if>
  </xsl:for-each>
  <xsl:text>&#xa;</xsl:text>
</xsl:template>

<!--put out a field escaping content-->
<xsl:template name="doThisField">
  <!--field value escaped per RFC4180-->
  <xsl:choose>
    <xsl:when test="contains(.,'&#x22;') or 
                    contains(.,',') or
                    contains(.,'&#xa;')">
      <xsl:text>"</xsl:text>
      <xsl:call-template name="escapeQuote"/>
      <xsl:text>"</xsl:text>
    </xsl:when>
    <xsl:otherwise><xsl:value-of select="."/></xsl:otherwise>
  </xsl:choose>
</xsl:template>

<!--escape a double quote in the current node value with two double quotes-->
<xsl:template name="escapeQuote">
  <xsl:param name="rest" select="."/>
  <xsl:choose>
    <xsl:when test="contains($rest,'&#x22;')">
      <xsl:value-of select="substring-before($rest,'&#x22;')"/>
      <xsl:text>""</xsl:text>
      <xsl:call-template name="escapeQuote">
        <xsl:with-param name="rest" select="substring-after($rest,'&#x22;')"/>
      </xsl:call-template>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$rest"/>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

</xsl:stylesheet>

Edited to remove superfluous template rule.

Nice solution - I'd not thought to include character escaping in my one.

JohnLBevan · Accepted Answer · 2013-09-27 07:41:16Z

1

<?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" indent="yes"/>
  <xsl:template match="/Root">
    <xsl:apply-templates select="Employers/Employer/Employee" />
  </xsl:template>
  <xsl:template match="/Root/Employers/Employer/Employee">
    <xsl:value-of select="../../../Metadata/id"/>
    <xsl:call-template name="delim" />
    <xsl:value-of select="../../../Metadata/name"/>
    <xsl:call-template name="delim" />
    <xsl:value-of select="../@id"/>
    <xsl:call-template name="delim" />
    <xsl:value-of select="@id"/>
    <xsl:call-template name="delim" />
    <xsl:value-of select="./Name"/>
    <xsl:call-template name="linebreak" />
  </xsl:template>
  <xsl:template name="delim">
    <xsl:text>, </xsl:text>
  </xsl:template>
  <xsl:template name="linebreak">
    <xsl:text>&#xA;</xsl:text>
  </xsl:template>
</xsl:stylesheet>

Use <xsl:text>
</xsl:text> (carriage return + line feed) in place of <xsl:text>
</xsl:text> (line feed) if you want windows style line endings (e.g. equivalent to \n vs \r\n in most languages).

NB: Delimiter and line break are in their own templates to make it easy for you to amend the characters without updating in multiple places / having to dig into the template defining used to pull the data together.

edited Sep 27, 2013 at 7:41

answered Sep 26, 2013 at 23:27

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

2 Comments

JohnLBevan Over a year ago

What happens when you try it? What XSL engine are you using (FYI: I wrote/tested in Visual Studio 2010, and also tested here: xslfiddle.net). I've amended to remove a couple of MS specific bits above in case that helps. . .

JohnLBevan Over a year ago

ps. Earlier I'd amended the XML in your question as it had been missing the end tag for the employee element - if that's also incorrect in your source XML that could be the cause of the issue; I'd assumed it was just a typo when posting the question so hadn't thought to highlight it before.

Collectives™ on Stack Overflow

Generate CSV output using XSL

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related