[Solved] XML to HTML table using XSL

by


Just as a different approach and also handling the colours for the same bus_types.
Demo

<?xml version="1.0"?>
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  version="1.0">
<xsl:output method="html"/>
<xsl:template match="https://stackoverflow.com/">
<table>
    <tr>
        <td colspan="9">ACME BUS SERVICE</td>
    </tr>
    <tr>
        <td colspan="9">
            Month: <xsl:value-of select="//Month"/>
        </td>
    </tr>
    <tr>
        <td>Season</td>
        <td>Location</td>
        <td>Bus Name</td>
        <td colspan="2">RED</td>
        <td colspan="2">GREEN</td>
        <td colspan="2">BLUE</td>
    </tr>
    <tr>
        <td></td>
        <td></td>
        <td></td>
        <td>Bus Type</td>
        <td>Bus Count</td>
        <td>Bus Type</td>
        <td>Bus Count</td>
        <td>Bus Type</td>
        <td>Bus Count</td>
    </tr>
    <xsl:for-each select="//row[Location[not(preceding::Location/. = .)]]" >
        <xsl:variable name="currentLocation" select="./Location"/>
        <tr>
            <xsl:attribute name="class">
              <xsl:value-of select="$currentLocation"/>
             </xsl:attribute>
            <td>
                <xsl:if test="position()=1">Winter</xsl:if>
            </td>
            <td>
                <xsl:value-of select="$currentLocation"/>
            </td>
            <td>
                <xsl:value-of select="./bus_name"/>
            </td>
            <td>
                <xsl:if test="count(//row[Location= $currentLocation]
                                         [bus_type = //row[Location= $currentLocation]
                                         [bus_colour="red"]/bus_type]) > 1">
                    <xsl:attribute name="class">color</xsl:attribute>
                </xsl:if>
                <xsl:value-of select="//row[Location= $currentLocation]
                                           [bus_colour="red"]/bus_type"/>
            </td>
            <td>
                <xsl:value-of select="//row[Location= $currentLocation]
                                           [bus_colour="red"]/bus_count"/>
            </td>
            <td>
                <xsl:if test="count(//row[Location= $currentLocation]
                                         [bus_type = //row[Location=$currentLocation]  
                                         [bus_colour="green"]/bus_type]) > 1">
                    <xsl:attribute name="class">color</xsl:attribute>
                </xsl:if>
                <xsl:value-of select="//row[Location= $currentLocation]
                                           [bus_colour="green"]/bus_type"/>
            </td>
            <td>
                <xsl:value-of select="//row[Location= $currentLocation]
                                           [bus_colour="green"]/bus_count"/>
            </td>
            <td>
                <xsl:if test="count(//row[Location= $currentLocation]
                                         [bus_type = //row[Location=$currentLocation]  
                                         [bus_colour="blue"]/bus_type]) > 1">
                    <xsl:attribute name="class">color</xsl:attribute>
                </xsl:if>
                <xsl:value-of select="//row[Location= $currentLocation]
                                           [bus_colour="blue"]/bus_type"/>
            </td>
            <td>
                <xsl:value-of select="//row[Location= $currentLocation]
                                           [bus_colour="blue"]/bus_count"/>
            </td>
        </tr>
    </xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>

For every location, the location is set as classname to the <tr>, e.g. <tr class="kansas">. Every td with a bus type that is used more than once at a location gets the class=”color”. So to display the table with different colors, you can just add CSS like e.g. .kansas .color { background-color: blue; }. In case you want to display the same color based on the bus_type, just adjust the classname “color” in the xslt to the current bus_type.

Note: In the linked example I’ve only added one row for Texas to show that the XSLT displays multiple locations, only sets the season for the first one, and will also work in case not all colors are provided for a location. And the output is not valid HTML (no html-, head-, body-tags etc provided). As you mentioned you’d like to get HTML ouput, you probably already have an XSLT generating valid HTML where you can adjust/include the part you need for the table.

Update for the question in the comments: To set the class name for a <tr> to the name of the bus_type (in case a bus_type is used more than once at a location) instead of the location:

Change this in above XSLT:

<tr>
<xsl:attribute name="class">
   <xsl:value-of select="$currentLocation"/>
</xsl:attribute>

into

<tr>
<xsl:if test="count(//row[Location=$currentLocation]) >  
              count(//row[Location=$currentLocation]/
                      bus_type[not(. = preceding::bus_type)])">  
    <xsl:attribute name="class">
       <xsl:value-of select="//row[Location=$currentLocation]/
                               bus_type[ . = preceding::bus_type]"/>
    </xsl:attribute>
</xsl:if>

Updated Demo 2 for this.

Additional notes and questions: One adjustment to the OP XML was to change the lowercase “volvo” to “Volvo”. In case the original export from DB really mixes upper- and lowercase names, this can be handled in the XSLT to lowercase all bus_names (to get the unique values) and uppercase the first letter for the value in the <td>. Also it would be good to know if you use XSLT 2.0 or XSLT 1.0 as XSLT 2.0 provides functionality to simplify some tasks – e.g. 2.0 provides a lower-case() function where in 1.0 the same can be achieved using translate() – as reference for this as you mentioned you’re new to XSL: How can I convert a string to upper- or lower-case with XSLT?
Further question is – as the XML example is only a part of the DB export – if there will be only one row for each location or if it is possible that there are various rows, e.g. kansas bus1, kansas bus2 etc.

Update 2 for the second question in the comments: I can add an (almost) line by line explanation and will drop a comment when done. I assume it’s not necessary to cover the HTML part but only the XSLT. In the meantime, as you mentioned you’re new to XSLT, maybe the following can be of use:
for <xsl:template match="https://stackoverflow.com/"> – https://stackoverflow.com/questions/3127108/xsl-xsltemplate-match

for XPath axes – http://www.xmlplease.com/axis
for some basics, e.g. – In what order do templates in an XSLT document execute, and do they match on the source XML or the buffered output?

Note that it should be avoided at SO to have extended comments – when there are too many comments below a post, an automated message will be displayed that suggests to move to chat. Because you need a reputation of 20 to chat (https://stackoverflow.com/help/privileges), this won’t be possible at the moment.

9

solved XML to HTML table using XSL