XML to CSV transformation using XSLT

Question

I am trying to convert my xml input to csv output as below details

INPUT file:

<Customer>
   <item>
      <CustomerID>100000069</CustomerID>
      <CustomerGroup>EX</CustomerGroup>
      <CustomerName>Test Mehmet</CustomerName>
      <CustomerStreet>Street</CustomerStreet>
      <HouseNumber>123</HouseNumber>
      <CustomerCity>Ismaning</CustomerCity>
      <CustomerZip></CustomerZip>
      <CustomerCountry>DE</CustomerCountry>
   </item>
</Customer>

XSL:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:variable name='newline'>
<xsl:text>
</xsl:text>
</xsl:variable>
<xsl:template match="/Customer">
<xsl:value-of select="concat('&quot;CustomerID&quot;;&quot;CustomerGroup&quot;;&quot;CustomerName&quot;;&quot;CustomerStreet&quot;;&quot;HouseNumber&quot;;&quot;CustomerCity&quot;;&quot;CustomerZIP&quot;;&quot;CustomerCountry&quot;',$newline)"/>
<xsl:for-each select="./item">
<xsl:value-of select="concat('&quot;',./CustomerID,'&quot;;&quot;',./CustomerGroup,'&quot;;&quot;',./CustomerName,'&quot;;&quot;',./CustomerStreet,'&quot;;&quot;',./HouseNumber,'&quot;;&quot;',./CustomerCity,'&quot;;&quot;',./CustomerZIP,'&quot;;&quot;',./CustomerCountry,'&quot;',$newline)"/>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

My Original output:

"CustomerID";"CustomerGroup";"CustomerName";"CustomerStreet";"HouseNumber";"CustomerCity";"CustomerZIP";"CustomerCountry" "100000069";"EX";"Test Mehmet";"Street";"123";"Ismaning";"";"DE"

Expected Output:

I need to change all empty values by 'null'. below is my expected output.

"CustomerID";"CustomerGroup";"CustomerName";"CustomerStreet";"HouseNumber";"CustomerCity";"CustomerZIP";"CustomerCountry" "100000069";"EX";"Test Mehmet";"Street";"123";"Ismaning";null;"DE"

Please suggest the additional change required to my code so it will populate null wherever "" comes.

Answer 1

Assuming you are limited to XSLT 1.0, I would suggest you do it this way:

XSLT 1.0

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

<xsl:template match="/Customer">
    <!-- header row -->
    <xsl:text>"CustomerID";"CustomerGroup";"CustomerName";"CustomerStreet";"HouseNumber";"CustomerCity";"CustomerZIP";"CustomerCountry"&#10;</xsl:text>
    <!-- data rows -->
    <xsl:for-each select="item">
        <xsl:call-template name="cell">
            <xsl:with-param name="data" select="CustomerID"/>
        </xsl:call-template>
        <xsl:text>;</xsl:text>
        <xsl:call-template name="cell">
            <xsl:with-param name="data" select="CustomerGroup"/>
        </xsl:call-template>
        <xsl:text>;</xsl:text>
        <xsl:call-template name="cell">
            <xsl:with-param name="data" select="CustomerName"/>
        </xsl:call-template>
        <xsl:text>;</xsl:text>
        <xsl:call-template name="cell">
            <xsl:with-param name="data" select="CustomerStreet"/>
        </xsl:call-template>
        <xsl:text>;</xsl:text>
        <xsl:call-template name="cell">
            <xsl:with-param name="data" select="HouseNumber"/>
        </xsl:call-template>
        <xsl:text>;</xsl:text>
        <xsl:call-template name="cell">
            <xsl:with-param name="data" select="CustomerCity"/>
        </xsl:call-template>
        <xsl:text>;</xsl:text>
        <xsl:call-template name="cell">
            <xsl:with-param name="data" select="CustomerZip"/>
        </xsl:call-template>
        <xsl:text>;</xsl:text>
        <xsl:call-template name="cell">
            <xsl:with-param name="data" select="CustomerCountry"/>
        </xsl:call-template>
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
</xsl:template>

<xsl:template name="cell">
    <xsl:param name="data"/>
    <xsl:choose>
        <xsl:when test="string($data)">
            <xsl:text>"</xsl:text>
            <xsl:value-of select="$data"/>
            <xsl:text>"</xsl:text>
        </xsl:when>
        <xsl:otherwise>null</xsl:otherwise>
    </xsl:choose>
</xsl:template>

</xsl:stylesheet>

This is a little verbose, but it avoids code repetition.

However, if you can be sure that all the elements will always be there, in the same order, even if some may be empty, you could shorten this to:

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

<xsl:template match="/Customer">
    <!-- header row -->
    <xsl:text>"CustomerID";"CustomerGroup";"CustomerName";"CustomerStreet";"HouseNumber";"CustomerCity";"CustomerZIP";"CustomerCountry"&#10;</xsl:text>
    <!-- data rows -->
    <xsl:for-each select="item">
        <xsl:for-each select="*">
            <xsl:choose>
                <xsl:when test="string(.)">
                    <xsl:text>"</xsl:text>
                    <xsl:value-of select="."/>
                    <xsl:text>"</xsl:text>
                </xsl:when>
                <xsl:otherwise>null</xsl:otherwise>
            </xsl:choose>
            <xsl:if test="position()!=last()">;</xsl:if>
        </xsl:for-each>
        <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>