Add class to FOR XML PATH depending on a column value

Question

I am building an html table to be injected into an email. I need this to be able to be ran directly out of sql. what I have so far is....

SELECT col1 AS 'td','',
    col2 AS 'td','',
    col3 AS 'td','',
    col4 AS 'td'
FROM TABLE
FOR XML PATH('tr'),ELEMENTS AS NVARCHAR(MAX)

I Would like to add a class to the row so that i can control the font of the whole row depending upon the value of say col3.

I come from the programming world so i am not familiar with the sql equivalent of the following... Pseudo Code Something like :

SELECT col1 AS 'td','',
    col2 AS 'td','',
    col3 AS 'td','',
    col4 AS 'td'
FROM TABLE
if col3 == 'fail' then FOR XML PATH('tr class=red'),ELEMENTS AS NVARCHAR(MAX)
else if col3 == 'warn' then XML PATH('tr class=yellow'),ELEMENTS AS NVARCHAR(MAX)
else FOR XML PATH('tr'),ELEMENTS AS NVARCHAR(MAX)

You might want to read [this answer](https://stackoverflow.com/a/39487565/5089204). There you'll find a function, which will transfer any `SELECT` into an HTML-table with column headers, CSS-support down to cell-level and some nice goodies... — Shnugo, Jan 23 '19 at 09:50

score 0 · Answer 1 · answered Jan 22 '19 at 22:53

Use a subquery building the row's cells as the row's content and a CASE to assign different values to the row's class attribute.

SELECT CASE col4
          WHEN 'fail' THEN
            'red'
          WHEN 'warn' THEN
            'yellow'
          ELSE
            'green'
        END 'tr/@class',
        (SELECT col1 'td',
               '',
               col2 'td',
               '',
               col3 'td',
               '',
               col4 'td'
               FOR XML PATH(''),
                       TYPE) 'tr'
        FROM elbat
        FOR XML PATH('table');

db<>fiddle

Add class to FOR XML PATH depending on a column value

1 Answers1