XSL alternate background color of HTML row results based on changing value of Node Element populated by SQL query

Question

I need help in setting up my HTML output from the XSL file to generate the row results to have an alternating background color based on when the value of a node element changes. The data is coming from SQL Server 2008 and the query results are sorted on the node element of "DivisionName".

SELECT      
  d.DivisionName,
  CASE
        WHEN t.ActualFinish is not NULL
              THEN convert(varchar, datepart(mm, t.ActualFinish))+ '/' + convert(varchar, datepart(dd, t.ActualFinish)) + '/' + convert(varchar, datepart(yyyy, t.ActualFinish))
        ELSE ' '
  END AS "Date",
  r.RegionName, 
  t.Name, 
  CASE
        WHEN t.ScheduleByElement is not NULL
              THEN t.ScheduleByElement
        ELSE ' '
  END AS "ScheduleByElement",
  '' AS "Activity",
  '' AS "Team",
  CASE
        WHEN t.WatchStatus is not NULL
              THEN convert(varchar, datepart(mm, t.WatchStatus))+ '/' + convert(varchar, datepart(dd, t.WatchStatus)) + '/' + convert(varchar, datepart(yyyy, t.WatchStatus))
        ELSE ' '
  END AS "Status"
FROM           
  dbo.Tasks AS t 
        INNER JOIN dbo.Division AS d 
              ON t.fk_DivisionID = d.DivisionID 
        INNER JOIN dbo.Region AS r 
              ON t.fk_RegionID = r.RegionID
WHERE        
  (t.OutlineLevel = 3) 
  AND (t.ScheduleByElement <> '') 
  AND (t.FileName LIKE 'NVDE%')
  AND (t.ActualFinish is not NULL)
ORDER BY d.DivisionName, t.ActualFinish, r.RegionName 
FOR XML PATH('DivisonName'), ROOT('CCDN')

Listed below are the complete contents of the XSL file:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" indent="yes"/>
<xsl:template match="/ROOT">


    <table border="1" cellpadding="6">
        <tr bgcolor="#9acd32">
            <td>Division</td>
            <td>Date</td>
            <td>Region</td>
            <td>System Name</td>
            <td>Schedule by/Plan by Element</td>
            <td>Activity</td>
            <td>Deployment Engineering Team</td>
            <td>Status</td>
        </tr>

        <xsl:for-each select="CCDN/DivisonName [not(preceding-sibling::DivisionName[1]/@DivisionNameID = @DivisionNameID)]">
            <xsl:variable name="DivisionNameID" select="@DivisionNameID" />
            <xsl:variable name="bgcolor">
                <xsl:choose>
                    <xsl:when test="position() mod 2 = 1">#FFFFCC</xsl:when>
                    <xsl:otherwise>#CCFFFF</xsl:otherwise>
                </xsl:choose>
            </xsl:variable>

            <xsl:for-each select=".|following-sibling::DivisionName[@DivisionNameID = $DivisionNameID]">
                <tr bgcolor="{$bgcolor}">
                    <xsl:for-each select="./*">
                        <td>
                            <xsl:value-of select="." />
                        </td>
                    </xsl:for-each>
                </tr>
            </xsl:for-each>
        </xsl:for-each>

    </table>


</xsl:template>

I've searched other questions similar to this on your site, but have not found an example where the color is defined on a changing value of a specific node element. Currently, the result is that the table is alternating the background color of every other row between #FFFFCC and #CCFFFF, regardless of the value of the DivisionName.

The following HTML probably better examplifies the results I'm seeking. The rows are sorted ascending according to the divisions, A, B, C and D. All of the rows associated with the first division are one shade (white) or color and all of the rows associated with the next are another shade or color and then repeat from that point on. If no rows are returned for A, then B will be white and alterante. There is no hard-coding of defining a specific shade or color for a specific division value. It alterantes regardless of the division returned and only cares if the division value changes.

<style type="text/css">
        table.mystyle
        {
        border-width: 0 0 1px 1px;
        border-spacing: 0;
        border-collapse: collapse;
        border-style: solid;
        }
        .mystyle td, .mystyle th
        {
        margin: 0;
        padding: 4px;
        border-width: 1px 1px 0 0;
        border-style: solid;
        }
        tr.oddcolor
        {
        Background-color:#FFFFFF;
        }
        tr.evencolor
        {
        Background-color:#CCCCCC;
        }
    </style>
<table class="mystyle">
<tr>
<b>
  <td>Division</td>
  <td>Date</td>
  <td>Region</td>
  <td>System Name</td>
  <td>Schedule by/Plan by Element</td>
  <td>Activity</td>
  <td>Deployment Engineering Team</td>
  <td>Status</td>
</b>
 </tr>
 <tr bgcolor="#FFFFCC">
  <td>A</td>
  <td>3/2/2012</td>
  <td>region</td>
  <td>xxx</td>
  <td>xxxx</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr bgcolor="#FFFFCC">
  <td>A</td>
  <td>4/12/2012</td>
  <td>region</td>
  <td>xxx</td>
  <td>xxxx</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr bgcolor="#FFFFCC">
  <td>A</td>
  <td>4/12/2012</td>
  <td>region</td>
  <td>xxx</td>
  <td>xxxx</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr bgcolor="#CCFFFF">
  <td>B</td>
  <td>4/27/2012</td>
  <td>region</td>
  <td>xxx</td>
  <td>xxxx</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr bgcolor="#CCFFFF">
  <td>B</td>
  <td>5/1/2012</td>
  <td>region</td>
  <td>xxx</td>
  <td>xxxx</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr bgcolor="#FFFFCC">
  <td>C</td>
  <td>5/15/2012</td>
  <td>region</td>
  <td>xxx</td>
  <td>xxxx</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr bgcolor="#FFFFCC">
  <td>C</td>
  <td>5/24/2012</td>
  <td>region</td>
  <td>xxx</td>
  <td>xxxx</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
<tr bgcolor="#CCFFFF">
  <td>D</td>
  <td>5/25/2012</td>
  <td>region</td>
  <td>xxx</td>
  <td>xxxx</td>
  <td></td>
  <td></td>
  <td></td>
</tr>
</table>

Thanks.

Answer 1

I'll have another bash then. This method relies on the data already being sorted by the division.

XML

<?xml version="1.0" encoding="UTF-8"?>
<root>
    <row division="a">Data</row>
    <row division="a">D1123ta</row>
    <row division="b">Da123ta</row>
    <row division="b">Da14ta</row>
    <row division="b">Da12312ta</row>
    <row division="c">Da123ta</row>
    <row division="c">Dat123a</row>
    <row division="c">Da123ta</row>
    <row division="c">Dat123a</row>
    <row division="d">Da123ta</row>
    <row division="d">Dat123a</row>
</root>

XSL

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">
    <xsl:output indent="yes"/>
    <xsl:key name="arb" match="/root/row" use="@division"/>
    <xsl:template match="/">
       <!-- <table>
            <xsl:for-each select="root/row">
                <xsl:if test="generate-id(.) = generate-id(key('arb',@division))">
                    <tr><td>New Colour</td></tr>
                </xsl:if>
                <tr><td><xsl:value-of select="."/></td></tr>
            </xsl:for-each>
        </table>-->
        <html>
            <body>
                <table>
            <xsl:for-each select="root/row[generate-id(.) = generate-id(key('arb',@division))]">
                <xsl:variable name="local_div" select="@division"/>
                <tbody>
                    <xsl:if test="position() mod 2 = 0">
                        <xsl:attribute name="bgcolor">red</xsl:attribute>
                    </xsl:if>
                    <xsl:for-each select="../row[@division=$local_div]">
                        <tr><td><xsl:value-of select="@division"/></td><td><xsl:value-of select="."/></td></tr>
                    </xsl:for-each>
                </tbody>
            </xsl:for-each>
        </table>
            </body>
        </html>

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

Product

<html>
<body>
  <table>
     <tbody>
        <tr>
           <td>a</td>
           <td>Data</td>
        </tr>
        <tr>
           <td>a</td>
           <td>D1123ta</td>
        </tr>
     </tbody>
     <tbody bgcolor="red">
        <tr>
           <td>b</td>
           <td>Da123ta</td>
        </tr>
        <tr>
           <td>b</td>
           <td>Da14ta</td>
        </tr>
        <tr>
           <td>b</td>
           <td>Da12312ta</td>
        </tr>
     </tbody>
     <tbody>
        <tr>
           <td>c</td>
           <td>Da123ta</td>
        </tr>
        <tr>
           <td>c</td>
           <td>Dat123a</td>
        </tr>
        <tr>
           <td>c</td>
           <td>Da123ta</td>
        </tr>
        <tr>
           <td>c</td>
           <td>Dat123a</td>
        </tr>
     </tbody>
     <tbody bgcolor="red">
        <tr>
           <td>d</td>
           <td>Da123ta</td>
        </tr>
        <tr>
           <td>d</td>
           <td>Dat123a</td>
        </tr>
     </tbody>
  </table>
</body>
</html>