Formating color in SQL Server using FOR XML PATH

Question

I already used this method, but now I have to use some colors depending on the values. So, I have the following information in a table:

Material | Q1 | Q2
---------+----+-----
M1       | 10 | 5
M2       | 3  | 10
M3       | 15 | 15

When the Q1 is lower than Q2 I want to color red, when is high in blue and yellow when is the same value.

CREATE TABLE #tempo
(
    q1 INT, q2 INT, name VARCHAR(10)
)

INSERT INTO #tempo (q1, q2, name)
VALUES (10, 5, 'low'), (10, 10, 'same'), (10, 20, 'high')

--SELECT * FROM #tempo

DECLARE @html varchar(MAX)
SET @html = '<table id="tablaPrincipal" border=0>
                    <tr style="background:#a7bfde;font-weight:bold;">
                        <td>q1</td>
                        <td>q2</td>
                        <td>Compare</td>
                    </tr>'+
                    (
                        SELECT 
                            isnull(q1,0) AS td
                            ,' ' , isnull(q2,0) AS td
                            ,' ' , name AS td

                    FROM #tempo
                    FOR XML PATH('tr')
                    )
                    +'</TABLE>'        
SELECT @html
DROP TABLE #tempo

I tried to use td with another tag tdx and replace like this: SET @html = REPLACE(@html, '', '')

but, it is possible to change the alias dynamically depending on the value?

Thanks!

This is presentation layer thing. You should use sql to return your data. Then build your html, including style, in the front end. — Sean Lange, Apr 27 '18 at 16:25
Gotcha. Then you are going to have to add style information on every line. Probably going to have to write some sql to create the row information instead of using XML PATH because you need to control this at a pretty detailed level. — Sean Lange, Apr 27 '18 at 16:49
You might have a [look here](https://stackoverflow.com/a/39487565/5089204) — Shnugo, Apr 28 '18 at 10:38

John Cappelletti · Answer 1 · 2018-04-27T17:33:59.313

6

Here is another option. Not clear if you wanted just the one cell highlighted

EDIT - Updated for 3 colors

Example

DECLARE @html varchar(MAX)
SET @html = '<table id="tablaPrincipal" border=0>
                    <tr style="background:#a7bfde;font-weight:bold;">
                        <td>q1</td>
                        <td>q2</td>
                        <td>Compare</td>
                    </tr>'+
                    (
                    SELECT [td/@style] = 'background:'+choose(sign(q1-q2)+2,'blue;color:white;','yellow','red')
                          ,[td] = isnull(q1,0)
                          ,null
                          ,[td/@style] = 'background:'+choose(sign(q1-q2)+2,'blue;color:white;','yellow','red')
                          ,[td] = isnull(q2,0) 
                          ,null
                          ,[td/@style] = 'background:'+choose(sign(q1-q2)+2,'blue;color:white;','yellow','red')
                          ,[td] = name 
                     FROM #tempo
                    FOR XML PATH('tr')
                    )
                    +'</TABLE>'        
SELECT @html

Returns

edited Apr 27 '18 at 17:33

answered Apr 27 '18 at 17:17

John Cappelletti

79,615
7
44
66

1

I figured there was a way to muscle that into the xml and I figured you would be the one to figure it out. Well done again sir. – Sean Lange Apr 27 '18 at 18:33
@SeanLange Actually you beat me to the punch with your solution – John Cappelletti Apr 27 '18 at 18:35
Mine was posted sooner but yours has a technique I was not familiar with using FOR XML. I don't do a lot of real work with xml so my chops are nearly non-existent beyond the basic build a delimited string type of thing. – Sean Lange Apr 27 '18 at 18:36
1

@SeanLange I follow Snugo ... one may say stalk :) – John Cappelletti Apr 27 '18 at 18:38
You follow me, but you write my name without a `h` :-D Did you ever have a look at [this answer](https://stackoverflow.com/a/39487565/5089204). At least I'm a bit proud of it :-D – Shnugo Apr 28 '18 at 10:44
One idea: As the full row gets the same layout, it was enough to use just the first `@style`, but without `td/`, remove the other ones. This would place the style as an attribute of ``, thus valid for the full row... (+1 from my side) – Shnugo Apr 28 '18 at 11:08
1

@Shnugo Please forgive the typo. We ALL knew who I was talking about :). I saw your linked answer some time ago (upvoted then) Full disclosure... FLWOR makes my head hurt. You are correct, I should have illustrated or at least commented on the TR style, but I wanted to demonstrate variable TD styles, hence my first line. – John Cappelletti Apr 28 '18 at 12:06
@JohnCappelletti, np, it's a good answer anyway. If the `@id` for *tablaPrincipal* is needed, my function would need an aditional parameter and include it similar to the top `@class` attribute. Might be a draw-back... – Shnugo Apr 28 '18 at 12:37

Shnugo · Answer 2 · 2018-04-28T11:12:33.947

With the function mentioned in my comments you'd go like this:

This is the function

CREATE FUNCTION dbo.CreateHTMLTable
(
    @SelectForXmlPathRowElementsXsinil XML
   ,@tblClass VARCHAR(100) --NULL to omit this class
   ,@thClass VARCHAR(100)  --same
   ,@tbClass VARCHAR(100)  --same
)
RETURNS XML
AS
BEGIN

RETURN 
(
    SELECT @tblClass AS [@class]  
    ,@thClass AS [thead/@class]
    ,@SelectForXmlPathRowElementsXsinil.query(
              N'let $first:=/row[1]
                return 
                <tr> 
                {
                for $th in $first/*
                return <th>{if(not(empty($th/@caption))) then xs:string($th/@caption) else local-name($th)}</th>
                }
                </tr>') AS thead
    ,@tbClass AS [tbody/@class]
    ,@SelectForXmlPathRowElementsXsinil.query(
               N'for $tr in /row
                 return 
                 <tr>{$tr/@class}
                 {
                 for $td in $tr/*
                 return
                 if(empty($td/@link)) 
                 then <td>{$td/@class}{string($td)}</td>
                 else <td>{$td/@class}<a href="{$td/@link}">{string($td)}</a></td>
                 }
                 </tr>') AS tbody
    FOR XML PATH('table'),TYPE
) 
END
GO

--Your test table

CREATE TABLE #tempo
(
    q1 INT, q2 INT, name VARCHAR(10)
);

INSERT INTO #tempo (q1, q2, name)
VALUES (10, 5, 'low'), (10, 10, 'same'), (10, 20, 'high');
GO

--Inline CSS for easy formatting

DECLARE @inlineCSS XML=
N'<style type="text/css" media="screen,print">
.low
{
    color: black;
    background-color: red;
}
.same
{
    color: black;
    background-color: yellow;
}
.high
{
    color: white;
    background-color: blue;
}
table,th
{
    border: 1px solid black;
}
</style>';

--This is the actual query

SELECT @inlineCSS 
      ,dbo.CreateHTMLTable
        (
             (
             SELECT 
               CASE WHEN ISNULL(q1,0)>ISNULL(q2,0) THEN 'low' 
                    ELSE CASE WHEN ISNULL(q2,0)>ISNULL(q1,0) THEN 'high' 
                         ELSE 'same' 
                         END 
                    END AS [@class] 
              ,name AS Material
              ,ISNULL(q1,0) AS [Q1]
              ,ISNULL(q2,0) AS [Q2]
             FROM #tempo 
             FOR XML PATH('row'),ELEMENTS XSINIL),NULL,NULL,NULL
        )
FOR XML PATH('body'),ROOT('html');

--Hint: Using classnames instead of the three ,NULL,NULL,NULL allows to place general CSS classes to the <table>, the <thead> and the <tbody>.

--Clean-Up

GO
DROP TABLE #tempo
GO
DROP FUNCTION dbo.CreateHTMLTable;

This is the result (click "Run" to see the output)

<html>
  <body>
    <style type="text/css" media="screen,print">
.low
{
    color: black;
    background-color: red;
}
.same
{
    color: black;
    background-color: yellow;
}
.high
{
    color: white;
    background-color: blue;
}
table,th
{
 border: 1px solid black;
}
</style>
    <table>
      <thead>
        <tr>
          <th>Material</th>
          <th>Q1</th>
          <th>Q2</th>
        </tr>
      </thead>
      <tbody>
        <tr class="low">
          <td>low</td>
          <td>10</td>
          <td>5</td>
        </tr>
        <tr class="same">
          <td>same</td>
          <td>10</td>
          <td>10</td>
        </tr>
        <tr class="high">
          <td>high</td>
          <td>10</td>
          <td>20</td>
        </tr>
      </tbody>
    </table>
  </body>
</html>

score 2 · Answer 3 · answered Apr 27 '18 at 16:59

How about something like this.

CREATE TABLE #tempo(
q1 INT, q2 INT, name VARCHAR(10)
)

INSERT INTO #tempo(q1,q2,name)VALUES(10,5,'low')
INSERT INTO #tempo(q1,q2,name)VALUES(10,10,'same')
INSERT INTO #tempo(q1,q2,name)VALUES(10,20,'high')

DECLARE @html varchar(MAX) = '<table id="tablaPrincipal" border=0>
                    <tr style="background:#a7bfde;font-weight:bold;">
                        <td>q1</td>
                        <td>q2</td>
                        <td>Compare</td>
                    </tr>'

SELECT @html = @html + '<tr style="background:' + case when q1 < q2 then 'red' when q1 > q2 then 'blue' else 'yellow' end + ';"><td>' + convert(varchar(10), q1) + '</td><td>' + convert(varchar(10), q2) + '</td></tr>'
FROM #tempo

select @html = @html + '</table>'

select @html
DROP TABLE #tempo

It is always dangerous to build XML (in this case XHTML) via string concatenation... If all values are `int` (as it seems to be here), this might be okay, but this approach is not the best in any case... I provided a function [here](https://stackoverflow.com/a/39487565/5089204) which will transform any `SELECT` into an HTML-table. And the *quirky update* might have some weird side effects too. — Shnugo, Apr 28 '18 at 10:40

Formating color in SQL Server using FOR XML PATH

3 Answers3

Linked