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.

Answer 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>&#xD;&#xA;</xsl:text> (carriage return + line feed) in place of <xsl:text>&#xA;</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.

Answer 2

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.