XML to CSV using xslt - handling missing elements with blank

Question

I'm trying to create generic xslt for XML to CSV.

Facing issues when elements not available in particular child doesn't produce blank value and if the node

Is it possible to achieve generic xslt for for different xmls which has similar tree structure with only difference in element nodes

Root- Child - Element1,Element2 Child - Element3 Child - Element4

Sample input XML

<?xml version="1.0" encoding="UTF-8"?>
<queryResponse>

  <User>
    <Id>rti</Id>

  </User>
  <User>
    <Id>qwe</Id>
    <EmployeeNumber>emp1</EmployeeNumber>
    <IsActive>false</IsActive>
  </User>
  <User>
    <Id>Abc</Id>
    <IsActive>false</IsActive>
  </User>
  <User>
    <Id>123</Id>
    <EmployeeNumber>emp4</EmployeeNumber>
    <IsActive>false</IsActive>

  </User>
</queryResponse>

Expected Output

"Id","EmployeeNumber","IsActive"
"rti","",""
"qwe","emp1","false"
"Abc","","false"
"123","emp4","false"

Tried XSLT

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

<xsl:param name="delim" select="','" />
<xsl:param name="break" select="'&#xA;'" /><!-- xA = NL, xD = CR -->
<xsl:param name="colnames" select="'y'"/>

<xsl:strip-space elements="*" />


<xsl:template match="/*/child::*">
<!--headerline-->
<xsl:if test="$colnames = 'y'">
 <!-- <xsl:if test="position() = 1"> -->
    <xsl:for-each select="child::*">
	<xsl:text>"</xsl:text> 
	<xsl:value-of select="name()"/>
	<xsl:text>"</xsl:text>  
             <xsl:if test="position() != last()">
	           <xsl:value-of select="$delim"/>
             </xsl:if>
      </xsl:for-each>
      <!-- hardcode version newline -->
	<!--<xsl:text>&#xa;</xsl:text>-->
      <!-- linebreak, nicer -->
      <xsl:value-of select="$break" />
<!--  </xsl:if>  -->
</xsl:if>


<!--dataline-->
<xsl:for-each select="child::*">

<xsl:if test="position() != last()">
      <xsl:text>"</xsl:text> 
      <xsl:value-of select="normalize-space(.)"/>
      <xsl:text>"</xsl:text> 
	  <xsl:value-of select="$delim" />
</xsl:if>

<xsl:if test="position()  = last()">
 <xsl:text>"</xsl:text>
<xsl:value-of select="normalize-space(.)"/>
 <xsl:text>"</xsl:text>
	  <xsl:value-of select="$break" />
</xsl:if>

</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Resulted output

"Id"
"rti"
"qwe","emp1","false"
"Abc","false"
"123","emp4","false"

Can you rephrase this please 'Facing issues when elements not available in particular child doesn't produce blank value and if the node'. It is not very clear what is your problem. — Kamal Soni

Martin Honnen Martin Honnen · Accepted Answer · 2018-02-21T09:48:30

You would need to define or detect the unique column names (i.e. third level element names) and then process each second level element to check whether it has such a column and if not output an empty string instead.

If you are not confined to XSLT 1 but could use XSLT 3 (as supported by Saxon 9.8 or Altova 2017/2018 or Exselt 1.1) you can do that in a compact way:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:mf="http://example.com/mf"
    exclude-result-prefixes="xs mf"
    version="3.0">

  <xsl:param name="sep" as="xs:string" select="','"/>
  <xsl:param name="lf" as="xs:string" select="'&#10;'"/>
  <xsl:param name="quote" as="xs:string" select="'&quot;'"/>

  <xsl:output method="text"/>
  <xsl:strip-space elements="*"/>

  <xsl:function name="mf:quote" as="xs:string">
      <xsl:param name="input" as="xs:string"/>
      <xsl:sequence select="$quote || replace($input, $quote, '$0$0') || $quote"/>
  </xsl:function>

  <xsl:param name="cols" as="xs:QName*" select="distinct-values(/*/*/*/node-name())"/>

  <xsl:template match="/">
      <xsl:value-of select="$cols!mf:quote(string())" separator="{$sep}"/>
      <xsl:value-of select="$lf"/>
      <xsl:apply-templates select="*/*"/>
  </xsl:template>

  <xsl:template match="*">
      <xsl:value-of select="for $col in $cols return mf:quote((*[node-name() eq $col], '')[1])" separator="{$sep}"/>
      <xsl:value-of select="$lf"/>
  </xsl:template>

</xsl:stylesheet>

Online sample at http://xsltfiddle.liberty-development.net/jyyiVhr.

And XSLT 2 version is at http://xsltransform.hikmatu.com/bFukv8h, it simply uses for .. return .. instead of ! and concat instead of ||:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:mf="http://example.com/mf"
    exclude-result-prefixes="xs mf"
    version="2.0">

  <xsl:param name="sep" as="xs:string" select="','"/>
  <xsl:param name="lf" as="xs:string" select="'&#10;'"/>
  <xsl:param name="quote" as="xs:string" select="'&quot;'"/>

  <xsl:output method="text"/>
  <xsl:strip-space elements="*"/>

  <xsl:function name="mf:quote" as="xs:string">
      <xsl:param name="input" as="xs:string"/>
      <xsl:sequence select="concat($quote, replace($input, $quote, '$0$0'), $quote)"/>
  </xsl:function>

  <xsl:param name="cols" as="xs:QName*" select="distinct-values(/*/*/*/node-name(.))"/>

  <xsl:template match="/">
      <xsl:value-of select="for $col in $cols return mf:quote(string($col))" separator="{$sep}"/>
      <xsl:value-of select="$lf"/>
      <xsl:apply-templates select="*/*"/>
  </xsl:template>

  <xsl:template match="*">
      <xsl:value-of select="for $col in $cols return mf:quote((*[node-name(.) eq $col], '')[1])" separator="{$sep}"/>
      <xsl:value-of select="$lf"/>
  </xsl:template>

</xsl:stylesheet>

XML to CSV using xslt - handling missing elements with blank

1 Answers