XML to CSV Applying XSLT (formatting issue, logic error)

Question

I am new to XSLT and XML. I have been going through tutorials and stackoverflow examples and learning a lot of new things.

Currently I am trying to apply XSLT on a XML file to transform it into CSV. What I am trying to achieve is that, I have some information in the XML file, which I would like to extract. The CSV file will have some information by default, which I am passing in the XSLT.

Also I have used basic XSLT syntax that I learnt quickly and I am sure this is the worst way to do it. I would appreciate all the help, if anyone can guide me how to make this solution better.

The issues I am having are as follows:

The information is not getting formatted properly, its getting printed on a new line, which I don't want unless I specify for a new line. Also if I don't indent the XSLT file then the information can be kept on single like but the code looks a mess, I am sure there is some easy way to do this.
Every person will have some licenses, which I want to read inside the for-each loop, but I am confused how to do that. As if the person doesn't have some licenses then by default I want it to be blank. Currently in this example I am using 5 states.

This is the xml file I am using to test.

<?xml version="1.0" encoding="UTF-8"?>
<People>
<Person>
    <required-tag1>some-information</required-tag1>
    <required-tag2>some-information</required-tag2>
    <first-name>Mike</first-name>
    <last-name>Hewitt</last-name>
    <licenses>
        <license>
            <state xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">IL</state>
        </license>
    </licenses>
</Person>
<Person>
    <required-tag1>some-information</required-tag1>
    <required-tag2>some-information</required-tag2>
    <first-name>John</first-name>
    <last-name>Jhonny</last-name>
    <licenses>
        <license>
            <state xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">NY</state>
        </license>
    </licenses>
</Person>
<Person>
    <required-tag1>some-information</required-tag1>
    <required-tag2>some-information</required-tag2>
    <first-name>Xmen</first-name>
    <last-name>Bat</last-name>
    <licenses>
        <license>
            <state xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">AK</state>
        </license>
        <license>
            <state xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">CA</state>
        </license>
        <license>
            <state xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">IL</state>
        </license>
        <license>
            <state xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">NY</state>
        </license>
        <license>
            <state xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">TX</state>
        </license>
    </licenses>
</Person>
</People>

The XSLT that I have written is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:csv="csv:csv" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output encoding="UTF-8" method="text"/>
<xsl:template match="/People">First Name,Last Name,AK,CA,IL,NY,TX,default value1,required-tag1,required-tag2,default value2,default value3
    <xsl:for-each select="Person">
        <xsl:value-of select="first-name"/>, <xsl:value-of select="last-name"/>,<xsl:choose>
    <xsl:when test="licenses/license/state='AK'">
      <xsl:value-of select="licenses/license/state"/>,
    </xsl:when>
    <xsl:otherwise>NA,
    </xsl:otherwise></xsl:choose><xsl:choose>
    <xsl:when test="licenses/license/state='CA'">
      <xsl:value-of select="licenses/license/state"/>,
    </xsl:when>
    <xsl:otherwise>NA,
    </xsl:otherwise></xsl:choose><xsl:choose>
    <xsl:when test="licenses/license/state='IL'">
      <xsl:value-of select="licenses/license/state"/>,
    </xsl:when>
    <xsl:otherwise>NA,
    </xsl:otherwise></xsl:choose><xsl:choose>
    <xsl:when test="licenses/license/state='NY'">
      <xsl:value-of select="licenses/license/state"/>,
    </xsl:when>
    <xsl:otherwise>NA,
    </xsl:otherwise></xsl:choose><xsl:choose>
    <xsl:when test="licenses/license/state='TX'">
      <xsl:value-of select="licenses/license/state"/>,
    </xsl:when>
    <xsl:otherwise>NA,
    </xsl:otherwise></xsl:choose>DEFAULT VALUE1,<xsl:value-of select="required-tag1"/>,<xsl:value-of select="required-tag2"/>,DEFAULT VALUE2,DEFAULT VALUE3<xsl:text>&#xa;</xsl:text>
    </xsl:for-each>
</xsl:template>
</xsl:stylesheet>

I tried using a for-each loop within the for-each loop for licenses but that executed 5 times for all licenses, how do I execute the condition to test for license-state 5 time and keep the format proper as per default.

This is how I want to output to be:

First Name,Last Name,AK,CA,IL,NY,TX,default value1,required-tag1,required-tag2,default value2,default value3
Mike, Hewitt,NA,NA,IL,NA,NA,DEFAULT VALUE1,some-information,some-information,DEFAULT VALUE2,DEFAULT VALUE3
John, Jhonny,NA,NA,NA,NY,NA,DEFAULT VALUE1,some-information,some-information,DEFAULT VALUE2,DEFAULT VALUE3
Xmen, Bat,AK,CA,IL,NY,TX,DEFAULT VALUE1,some-information,some-information,DEFAULT VALUE2,DEFAULT VALUE3

I am using XSLT version 1.0.

Thank you, for helping.

For reference, a similar question has been asked here: http://stackoverflow.com/questions/365312/xml-to-csv-using-xslt You might find a few useful ideas there. — Tomalak, Sep 30 '14 at 20:52

score 3 · Accepted Answer · answered Sep 30 '14 at 20:49

For the issue with new lines, consider how you are currently writing NA as an example

<xsl:otherwise>NA,
</xsl:otherwise>

Now, if you had just this...

<xsl:otherwise>
</xsl:otherwise>

Then XSLT would not output a new line at all. There is a text node under xsl:otherwise but it is a whitespace only node, so it is ignored. But as soon as you add normal text to it, like NA then XSLT considers all the text important, and outputs it including the new-line. It does not normalize any spaces.

The solution is to wrap all such text in xsl:text

<xsl:otherwise>
   <xsl:text>NA</xsl:text>
</xsl:otherwise>

As for your logic issue, you are currently doing this to check for a state

<xsl:when test="licenses/license/state='IL'">
  <xsl:value-of select="licenses/license/state"/>,
</xsl:when>

But there is a subtle difference between the use of licenses/license/state in each statement. In the xsl:when you are asking "Is there a licence with this state", but in your xsl:value-of it will get the first state in the list!

Now, you could write this

<xsl:when test="licenses/license/state='IL'">
  <xsl:value-of select="licenses/license[state='IL']/state"/>
  <xsl:text>,</xsl:text>
</xsl:when>

But this is rather superfluous. Better to simplify it to this

<xsl:when test="licenses/license/state='IL'">
  <xsl:text>IL,</xsl:text>
</xsl:when>

It is also worth mentioning you clearly have a lot of repetitive code, so you could remove some of this by using a named template.

Try this XSLT as an example

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

<xsl:template match="/People">
  <xsl:text>First Name,Last Name,AK,CA,IL,NY,TX,default value1,required-tag1,required-tag2,default value2,default value3</xsl:text>
  <xsl:text>&#xa;</xsl:text>
    <xsl:for-each select="Person">
        <xsl:value-of select="first-name"/>
        <xsl:text>, </xsl:text>
        <xsl:value-of select="last-name"/>
        <xsl:text>, </xsl:text>
        <xsl:call-template name="state">
          <xsl:with-param name="stateCode" select="'AK'" />
        </xsl:call-template>
      <xsl:call-template name="state">
          <xsl:with-param name="stateCode" select="'CA'" />
        </xsl:call-template>
       <xsl:call-template name="state">
          <xsl:with-param name="stateCode" select="'IL'" />
        </xsl:call-template>
      <xsl:call-template name="state">
          <xsl:with-param name="stateCode" select="'NY'" />
        </xsl:call-template>
        <xsl:call-template name="state">
          <xsl:with-param name="stateCode" select="'TX'" />
        </xsl:call-template>
        <xsl:text>DEFAULT VALUE1,</xsl:text>
        <xsl:value-of select="required-tag1"/>
        <xsl:text>,</xsl:text>
        <xsl:value-of select="required-tag2"/>
        <xsl:text>,DEFAULT VALUE2,DEFAULT VALUE3</xsl:text>
        <xsl:text>&#xa;</xsl:text>
    </xsl:for-each>
</xsl:template>

<xsl:template name="state">
  <xsl:param name="stateCode" />
  <xsl:choose>
      <xsl:when test="licenses/license[state=$stateCode]">
        <xsl:value-of select="$stateCode"/>
      </xsl:when>
      <xsl:otherwise>
        <xsl:text>NA</xsl:text>
      </xsl:otherwise>
  </xsl:choose>
  <xsl:text>,</xsl:text>
</xsl:template>
</xsl:stylesheet>

This is still quite repetitive, with the repeated uses of xsl:call-template. if you did just want only 5 fixed states, you could put the states in an external XML document, and loop over that, as an example. That would be another question though.....

Thank you very much for such a simple and easy to understand descriptive answer. I have just learnt a lot of things in this solution. — Aniks, Sep 30 '14 at 21:23

XML to CSV Applying XSLT (formatting issue, logic error)

1 Answers1