XSLT to transform to HTML and format the HTML based on data in XML

Question

i am pretty new to XSLT / XML and HTML. I have a XML file that I currently convert to HTML in c# using an XSLT. The XML file represents nothing but data extracted from a table in a database. I can currently convert the XML file to HTML using XSLT fairly easily without much formating. the HTML when opened looks pretty ordinary. What i intend to is format the HTML i.e change the font, background color, font color etc based on certain key values in the XML document.

The XML is generated on a daily basis using C# code . the content of the XML file totally depends on the contents of the table in the database at that point in the day when the C# code is executed..

the XML looks something like this

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <defects>
    <Defectid>56</Defectid>
    <testid>111</testid>
    <summary>Release of DIT </summary>
    <DetectedDate>2011-09-21 </DetectedDate>
    <priority>2-Give High Attention</priority>
    <status>Ready to Test</status>
    <project>Business Intelligence</project>
    <assignedTo>peter</assignedTo>
    <detectedBy>john</detectedBy>
    <severity>3-Average</severity>
  </defects>
  <defects>
    <Defectid>829</Defectid>
    <testid>111</testid>
    <summary> Data request</summary>
    <DetectedDate>2012-01-12 </DetectedDate>
    <priority>3-Normal Queue</priority>
    <status>Open</status>
    <project>web</project>
    <assignedTo>tcm</assignedTo>
    <detectedBy>john</detectedBy>
    <severity>3-Average</severity>
  </defects>
  <defects>
    <Defectid>728</Defectid>
    <testid>999</testid>
    <summary>Data request</summary>
    <DetectedDate>2012-01-11</DetectedDate>
    <priority>3-Normal Queue</priority>
    <status>Fixed</status>
    <project>Business Intelligence</project>
    <assignedTo>chris</assignedTo>
    <detectedBy>peter</detectedBy>
    <severity>3-Average</severity>
  </defects>
</NewDataSet>

what i intend to do is generate and HTML table from this XML which would be in tabular format but the font color of the rows in the HTML table should be set based on "testid" attribute . i.e. for font color on the HTML should be unique per "testid" attribute. Since the rows per testid would change daily based on the data in the table in the database, I am not sure how this can be accomplished using XSLT.

the current XSLT looks something like this.. as you can see , I have hardcoded the font colors.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html"/>
  <xsl:template match="/">
    <table  BORDER="1" CELLPADDING="3" CELLSPACING="2" WIDTH="100">
      <tr>
        <td nowrap="nowrap" width = "70">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Defect ID</b>
          </h1>
        </td>
        <td nowrap="nowrap" width = "70">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Test ID</b>
          </h1>
        </td>
        <td nowrap="nowrap" width = "400">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Summary</b>
          </h1>
        </td>
        <td nowrap="nowrap" width = "150">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Detected Date</b>
          </h1>
        </td>
        <td width = "200">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Priority</b>
          </h1>
        </td>
        <td width = "200">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Status</b>
          </h1>
        </td>
        <td width = "200">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Project</b>
          </h1>
        </td>
        <td nowrap="nowrap" width = "100">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Assigned To</b>
          </h1>
        </td>
        <td nowrap="nowrap" width = "100">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Detected By</b>
          </h1>
        </td>
        <td nowrap="nowrap" width = "80">
          <h1 style="font-family:verdana ;font-size:60%;color:green">
            <b>Severity</b>
          </h1>
        </td>
      </tr>
      <xsl:for-each select="//defects">

        <tr>
          <td width = "100">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="Defectid"></xsl:value-of>
            </h1>
          </td>
          <td width = "100">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="testid"/>
            </h1>
          </td>
          <td width = "400">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="summary"/>
            </h1>
          </td>
          <td width = "100">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="DetectedDate"/>
            </h1>
          </td>
          <td width = "100">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="priority"/>
            </h1>
          </td>
          <td width = "100">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="status"/>
            </h1>
          </td>
          <td width = "100">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="project"/>
            </h1>
          </td>
          <td width = "100">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="assignedTo"/>
            </h1>
          </td>
          <td width = "100">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="detectedBy"/>
            </h1>
          </td>
          <td width = "100">
            <h1 style="font-family:verdana ;font-size:60%;color:blue">
              <xsl:value-of select="severity"/>
            </h1>
          </td>

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

</xsl:stylesheet>

Does anybody know or could anybody guide me?

Answer 1

Below is a solution that applies up to 20 different colors - applying one particular color for each row with a specific testid.
Note that it is not important how many distinct testid's occur. Also note that color coding says nothing about the testid itself - but that's exactly the way you wanted it :-).

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet
    version="2.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:std="http://www.standardColors.com">

    <xsl:output method="html"/>

    <xsl:variable name="colors">
        <color>#0000FF</color>
        <color>#FF0000</color>
        <color>#00FFFF</color>
        <color>#FFFF00</color>
        <color>#347C2C</color>
        <color>#800080</color>
        <color>#3B9C9C</color>
        <color>#A52A2A</color>
        <color>#3BB9FF</color>
        <color>#FF00FF</color>
        <color>#6698FF</color>
        <color>#808000</color>
        <color>#8D38C9</color>
        <color>#ADD8E6</color>
        <color>#F660AB</color>
        <color>#F87217</color>
        <color>#F9B7FF</color>
        <color>#FFA500</color>
        <color>#FFE87C</color>
        <color>#8E35EF</color>
    </xsl:variable>

    <xsl:variable name="testIDs" select="distinct-values(//testid)"/>

    <xsl:variable name="colorList">
        <xsl:for-each select="$testIDs">
            <xsl:variable name="pos" select="position() mod 20"/>
            <xsl:copy-of select="$colors/color[$pos]"/>
        </xsl:for-each>
    </xsl:variable>

    <xsl:template match="/">
        <html>
            <head>
                <style>
                    th, td {
                        border: solid black 1px ;
                        padding: 3px;                       
                        border-spacing:2px;
                        border-collapse: collapse;
                        width: 100px;
                    }
                    th {
                        font-family:verdana;
                        font-size:60%;
                        color:green;
                        align:center;
                        white-space: nowrap;
                    }
                    <xsl:for-each select="$testIDs">
                    <xsl:variable name="pos" select="position()"/>
                     tr.testid<xsl:value-of select="."/> {
                        font-family:verdana;
                        font-size:60%;
                        font-weight:bold;
                        color:<xsl:value-of select="$colorList/color[$pos]"/>;
                        align:center
                    }
                   </xsl:for-each>
                </style>
            </head>                
            <body>
                <table>
                    <tr>
                        <xsl:for-each select="/NewDataSet/defects[1]/*">
                            <th>
                                <xsl:value-of select="name(.)"/>
                            </th>
                        </xsl:for-each>
                    </tr>
                    <xsl:apply-templates select="*"/>
                </table>
            </body>
        </html>
   </xsl:template>

    <xsl:template match="/NewDataSet/defects">
        <tr>
            <xsl:attribute name="class">testid<xsl:value-of select="testid"/></xsl:attribute>
            <xsl:for-each select="*">
                <td>
                    <xsl:value-of select="."/>
                </td>
            </xsl:for-each>
        </tr>
    </xsl:template>

</xsl:stylesheet>

I applied this on the below xml:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <defects>
    <Defectid>56</Defectid>
    <testid>111</testid>
    <summary>Release of DIT </summary>
    <DetectedDate>2011-09-21 </DetectedDate>
    <priority>2-Give High Attention</priority>
    <status>Ready to Test</status>
    <project>Business Intelligence</project>
    <assignedTo>peter</assignedTo>
    <detectedBy>john</detectedBy>
    <severity>3-Average</severity>
  </defects>
  <defects>
    <Defectid>829</Defectid>
    <testid>111</testid>
    <summary> Data request</summary>
    <DetectedDate>2012-01-12 </DetectedDate>
    <priority>3-Normal Queue</priority>
    <status>Open</status>
    <project>web</project>
    <assignedTo>tcm</assignedTo>
    <detectedBy>john</detectedBy>
    <severity>3-Average</severity>
  </defects>
  <defects>
    <Defectid>728</Defectid>
    <testid>999</testid>
    <summary>Data request</summary>
    <DetectedDate>2012-01-11</DetectedDate>
    <priority>3-Normal Queue</priority>
    <status>Fixed</status>
    <project>Business Intelligence</project>
    <assignedTo>chris</assignedTo>
    <detectedBy>peter</detectedBy>
    <severity>3-Average</severity>
  </defects>
  <defects>
    <Defectid>728</Defectid>
    <testid>321</testid>
    <summary>Data request</summary>
    <DetectedDate>2012-01-11</DetectedDate>
    <priority>3-Normal Queue</priority>
    <status>Fixed</status>
    <project>Business Intelligence</project>
    <assignedTo>chris</assignedTo>
    <detectedBy>peter</detectedBy>
    <severity>3-Average</severity>
  </defects>
  <defects>
    <Defectid>728</Defectid>
    <testid>457</testid>
    <summary>Data request</summary>
    <DetectedDate>2012-01-11</DetectedDate>
    <priority>3-Normal Queue</priority>
    <status>Fixed</status>
    <project>Business Intelligence</project>
    <assignedTo>chris</assignedTo>
    <detectedBy>peter</detectedBy>
    <severity>3-Average</severity>
  </defects>
  <defects>
    <Defectid>728</Defectid>
    <testid>202</testid>
    <summary>Data request</summary>
    <DetectedDate>2012-01-11</DetectedDate>
    <priority>3-Normal Queue</priority>
    <status>Fixed</status>
    <project>Business Intelligence</project>
    <assignedTo>chris</assignedTo>
    <detectedBy>peter</detectedBy>
    <severity>3-Average</severity>
  </defects>
</NewDataSet>

And got as result

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <style>
                    th, td {
                        border: solid black 1px ;
                        padding: 3px;                       
                        border-spacing:2px;
                        border-collapse: collapse;
                        width: 100px;
                    }
                    th {
                        font-family:verdana;
                        font-size:60%;
                        color:green;
                        align:center;
                        white-space: nowrap;
                    }

                     tr.testid111 {
                        font-family:verdana;
                        font-size:60%;
                        font-weight:bold;
                        color:#0000FF;
                        align:center
                    }

                     tr.testid999 {
                        font-family:verdana;
                        font-size:60%;
                        font-weight:bold;
                        color:#FF0000;
                        align:center
                    }

                     tr.testid321 {
                        font-family:verdana;
                        font-size:60%;
                        font-weight:bold;
                        color:#00FFFF;
                        align:center
                    }

                     tr.testid457 {
                        font-family:verdana;
                        font-size:60%;
                        font-weight:bold;
                        color:#FFFF00;
                        align:center
                    }

                     tr.testid202 {
                        font-family:verdana;
                        font-size:60%;
                        font-weight:bold;
                        color:#347C2C;
                        align:center
                    }
                   </style>
    </head>
    <body>
        <table>
            <tr>
                <th>Defectid</th>
                <th>testid</th>
                <th>summary</th>
                <th>DetectedDate</th>
                <th>priority</th>
                <th>status</th>
                <th>project</th>
                <th>assignedTo</th>
                <th>detectedBy</th>
                <th>severity</th>
            </tr>
            <tr class="testid111">
                <td>56</td>
                <td>111</td>
                <td>Release of DIT </td>
                <td>2011-09-21 </td>
                <td>2-Give High Attention</td>
                <td>Ready to Test</td>
                <td>Business Intelligence</td>
                <td>peter</td>
                <td>john</td>
                <td>3-Average</td>
            </tr>
            <tr class="testid111">
                <td>829</td>
                <td>111</td>
                <td> Data request</td>
                <td>2012-01-12 </td>
                <td>3-Normal Queue</td>
                <td>Open</td>
                <td>web</td>
                <td>tcm</td>
                <td>john</td>
                <td>3-Average</td>
            </tr>
            <tr class="testid999">
                <td>728</td>
                <td>999</td>
                <td>Data request</td>
                <td>2012-01-11</td>
                <td>3-Normal Queue</td>
                <td>Fixed</td>
                <td>Business Intelligence</td>
                <td>chris</td>
                <td>peter</td>
                <td>3-Average</td>
            </tr>
            <tr class="testid321">
                <td>728</td>
                <td>321</td>
                <td>Data request</td>
                <td>2012-01-11</td>
                <td>3-Normal Queue</td>
                <td>Fixed</td>
                <td>Business Intelligence</td>
                <td>chris</td>
                <td>peter</td>
                <td>3-Average</td>
            </tr>
            <tr class="testid457">
                <td>728</td>
                <td>457</td>
                <td>Data request</td>
                <td>2012-01-11</td>
                <td>3-Normal Queue</td>
                <td>Fixed</td>
                <td>Business Intelligence</td>
                <td>chris</td>
                <td>peter</td>
                <td>3-Average</td>
            </tr>
            <tr class="testid202">
                <td>728</td>
                <td>202</td>
                <td>Data request</td>
                <td>2012-01-11</td>
                <td>3-Normal Queue</td>
                <td>Fixed</td>
                <td>Business Intelligence</td>
                <td>chris</td>
                <td>peter</td>
                <td>3-Average</td>
            </tr>
        </table>
    </body>
</html>

which in a browser looks as follows:

ADDED variant using key

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet
    version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:std="http://www.standardColors.com"
    exclude-result-prefixes="std">

    <xsl:output method="html"/>

    <xsl:variable name="colors">
        <color>#0000FF</color>
        <color>#FF0000</color>
        <color>#00FFFF</color>
        <color>#FFFF00</color>
        <color>#347C2C</color>
        <color>#800080</color>
        <color>#3B9C9C</color>
        <color>#A52A2A</color>
        <color>#3BB9FF</color>
        <color>#FF00FF</color>
        <color>#6698FF</color>
        <color>#808000</color>
        <color>#8D38C9</color>
        <color>#ADD8E6</color>
        <color>#F660AB</color>
        <color>#F87217</color>
        <color>#F9B7FF</color>
        <color>#FFA500</color>
        <color>#FFE87C</color>
        <color>#8E35EF</color>
    </xsl:variable>

    <!--<xsl:variable name="testIDs" select="distinct-values(//testid)"/>-->

    <xsl:key name="testidKey" match="testid" use="text()"/>

    <xsl:variable name="colorList">
        <xsl:for-each select="//testid">
            <xsl:if test="generate-id() = generate-id(key('testidKey', text())[1])">
                <xsl:variable name="pos" select="position() mod 20"/>
                <color>
                    <xsl:attribute name="testid"><xsl:value-of select="."/></xsl:attribute>
                    <xsl:value-of select="$colors/color[$pos]"/>
                </color>
            </xsl:if>
        </xsl:for-each>
    </xsl:variable>

    <xsl:template match="/">
        <html>
            <head>
                <style>
                    th, td {
                        border: solid black 1px ;
                        padding: 3px;                       
                        border-spacing:2px;
                        border-collapse: collapse;
                        width: 100px;
                    }
                    th {
                        font-family:verdana;
                        font-size:60%;
                        color:green;
                        align:center;
                        white-space: nowrap;
                    }
                    <xsl:for-each select="$colorList/color">
                     tr.testid<xsl:value-of select="@testid"/> {
                        font-family:verdana;
                        font-size:60%;
                        font-weight:bold;
                        color:<xsl:value-of select="."/>;
                        align:center
                    }
                   </xsl:for-each>
                </style>
            </head>                
            <body>
                <table>
                    <tr>
                        <xsl:for-each select="/NewDataSet/defects[1]/*">
                            <th>
                                <xsl:value-of select="name(.)"/>
                            </th>
                        </xsl:for-each>
                    </tr>
                    <xsl:apply-templates select="*"/>
                </table>
            </body>
        </html>
   </xsl:template>

    <xsl:template match="/NewDataSet/defects">
        <tr>
            <xsl:attribute name="class">testid<xsl:value-of select="testid"/></xsl:attribute>
            <xsl:for-each select="*">
                <td>
                    <xsl:value-of select="."/>
                </td>
            </xsl:for-each>
        </tr>
    </xsl:template>

</xsl:stylesheet>

MSXML error prevention (and any other xsl engine used)

see convert RTF into node-set and RTF to node-set generic approach for further details.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet
    version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:std="http://www.standardColors.com"
    xmlns:exslt="http://www.exslt.org/common"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    exclude-result-prefixes="std">

    <xsl:output method="html"/>

    <std:colors>
        <color>#0000FF</color>
        <color>#FF0000</color>
        <color>#00FFFF</color>
        <color>#FFFF00</color>
        <color>#347C2C</color>
        <color>#800080</color>
        <color>#3B9C9C</color>
        <color>#A52A2A</color>
        <color>#3BB9FF</color>
        <color>#FF00FF</color>
        <color>#6698FF</color>
        <color>#808000</color>
        <color>#8D38C9</color>
        <color>#ADD8E6</color>
        <color>#F660AB</color>
        <color>#F87217</color>
        <color>#F9B7FF</color>
        <color>#FFA500</color>
        <color>#FFE87C</color>
        <color>#8E35EF</color>
    </std:colors>

    <xsl:variable name="colors" select="document('')/*/std:colors"/>

    <xsl:key name="testidKey" match="testid" use="text()"/>

    <xsl:variable name="std:colorList">
        <xsl:for-each select="//testid">
            <xsl:if test="generate-id() = generate-id(key('testidKey', text())[1])">
                <xsl:variable name="pos" select="position() mod 20"/>
                <xsl:element name="color">
                    <xsl:attribute name="testid"><xsl:value-of select="."/></xsl:attribute>
                    <xsl:value-of select="$colors/color[$pos + 1]"/>
                </xsl:element>
            </xsl:if>
        </xsl:for-each>
    </xsl:variable>

    <xsl:template match="/">
        <html>
            <head>
                <style>
                    table {
                        font-family:verdana;
                        font-size:60%;
                        font-weight:bold;
                        align:center;
                        white-space: nowrap;
                    }
                    th, td {
                        border: solid black 1px ;
                        padding: 3px;                       
                        border-spacing:2px;
                        border-collapse: collapse;
                        width: 100px;
                    }
                    th {
                        color:green;
                    }
                    <xsl:choose>
                        <xsl:when test="function-available('msxsl:node-set')">
                            <xsl:apply-templates select="msxsl:node-set($std:colorList)/color" mode="addTRclassToCSS"/>
                        </xsl:when>
                        <xsl:when test="function-available('exslt:node-set')">
                            <xsl:apply-templates select="exslt:node-set($std:colorList)/color" mode="addTRclassToCSS"/>
                        </xsl:when>
                        <xsl:otherwise>
                            <xsl:variable name="colorList" select="$std:colorList"/>
                            <xsl:apply-templates select="$colorList" mode="addTRclassToCSS"/>
                        </xsl:otherwise>
                    </xsl:choose>

                </style>
            </head>                
            <body>
                <table>
                    <tr>
                        <xsl:for-each select="/NewDataSet/defects[1]/*">
                            <th>
                                <xsl:value-of select="name(.)"/>
                            </th>
                        </xsl:for-each>
                    </tr>
                    <xsl:apply-templates select="*"/>
                </table>
            </body>
        </html>
   </xsl:template>

    <xsl:template match="/NewDataSet/defects">
        <tr>
            <xsl:attribute name="class">testid<xsl:value-of select="testid"/></xsl:attribute>
            <xsl:for-each select="*">
                <td>
                    <xsl:value-of select="."/>
                </td>
            </xsl:for-each>
        </tr>
    </xsl:template>

    <xsl:template match="color" mode="addTRclassToCSS">
                    tr.testid<xsl:value-of select="@testid"/> {
                    color:<xsl:value-of select="."/>;
                    }
    </xsl:template>
</xsl:stylesheet>

Answer 2

If I'm following your scenario correctly, here's what I suggest:

Use the data from your XML "testid" element as the value for a class attribute that you assign to your <h1> tags in your XSLT. Then use css to define the colors that are used with the particular testid values.

Since in your sample your "testid" values are all numbers, do remember to give a hard-coded alphabetic prefix to the class name. CSS doesn't like class names beginning with numbers.

Answer 3

Making the assumption that testid's 111 should be blue and all others red then try this:

<?xml version="1.0" encoding="iso-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html"/>

   <xsl:template match="/">
        <html>
            <head>
                <style>
                    h1.header{
                        font-family:verdana;
                        font-size:60%;
                        color:green                    
                    }
                    h1.testid111 {
                    font-family:verdana ;font-size:60%;color:blue
                    }
                    h1.testid999 {
                    font-family:verdana ;font-size:60%;color:red
                    }
                </style>
            </head>                
            <body>
                <table BORDER="1" CELLPADDING="3" CELLSPACING="2" WIDTH="100">
                    <tr>
                        <xsl:for-each select="/NewDataSet/defects[1]/*">
                            <td nowrap="nowrap" width="70">
                                <h1 class="header">
                                    <b>
                                        <xsl:value-of select="name(.)"/>
                                    </b>
                                </h1>
                            </td>
                        </xsl:for-each>
                    </tr>
                    <xsl:apply-templates select="*"/>
                </table>
            </body>
        </html>
   </xsl:template>
    <xsl:template match="/NewDataSet/defects">
        <xsl:variable name="class">
            <xsl:text>testid</xsl:text><xsl:value-of select="testid"/>
        </xsl:variable>
            <tr>
                <xsl:for-each select="*">
                    <td nowrap="nowrap" width = "70">
                        <h1 class='{$class}'>
                            <b>
                                <xsl:value-of select="."/>
                            </b>
                        </h1>
                    </td>
                </xsl:for-each>
            </tr>

    </xsl:template>
</xsl:stylesheet>