How do I modify my XML codes in this SQL Query to highlight specific rows of the table?

Question

I am using SQL Server 2012 and I have the following SQL codes which output HTML codes for a Table. The output is stored in an nvarchar (max) variable.

Shown below are the codes and its corresponding output (in the form of an HTML table; note: the true output is a set html codes):

declare @tableHtml nvarchar(max)
declare @style nvarchar(50) = 'border-bottom:1px solid #7AC0DA'
declare @MyTable table
(
    StayYear nvarchar(10),
    PropertyCode nvarchar(10),
    Jan nvarchar(10),
    Feb nvarchar(10),
    Mar nvarchar(10),
    Apr nvarchar(10),
    May nvarchar(10),
    Jun nvarchar(10),
    Jul nvarchar(10),
    Aug nvarchar(10),
    Sep nvarchar(10),
    Oct nvarchar(10),
    Nov nvarchar(10),
    Dec nvarchar(10),
    Total nvarchar(50)
)
insert into @MyTable 
SELECT * FROM ITB   

select @tableHtml = (    

select
    '1' as '@border',
    '4' as '@cellpadding',
    'font-size:12px; font-family:Arial' as '@style',
    (
        select  (select @style as '@style', 'StayYear' as '*' for xml path('th'), type),
                (select @style as '@style', 'PropertyCode' as '*' for xml path('th'), type),
                (select @style as '@style', 'Jan' as '*' for xml path('th'), type),
                (select @style as '@style', 'Feb' as '*' for xml path('th'), type),
                (select @style as '@style', 'Mar' as '*' for xml path('th'), type),
                (select @style as '@style', 'Apr' as '*' for xml path('th'), type),
                (select @style as '@style', 'May' as '*' for xml path('th'), type),
                (select @style as '@style', 'Jun' as '*' for xml path('th'), type),
                (select @style as '@style', 'Jul' as '*' for xml path('th'), type),
                (select @style as '@style', 'Aug' as '*' for xml path('th'), type),
                (select @style as '@style', 'Sep' as '*' for xml path('th'), type),
                (select @style as '@style', 'Oct' as '*' for xml path('th'), type),
                (select @style as '@style', 'Nov' as '*' for xml path('th'), type),
                (select @style as '@style', 'Dec' as '*' for xml path('th'), type),
                (select @style as '@style', 'Total' as '*' for xml path('th'), type)


        for xml path('tr'), type
    ),
    (
        select 'trclass' as '@class',
                (select StayYear as '*' for xml path('td'), type),
                (select PropertyCode as '*' for xml path('td'), type),
                (select Jan as '*' for xml path('td'), type),
                (select Feb as '*' for xml path('td'), type),
                (select Mar as '*' for xml path('td'), type),
                (select Apr as '*' for xml path('td'), type),
                (select May as '*' for xml path('td'), type),
                (select Jun as '*' for xml path('td'), type),
                (select Jul as '*' for xml path('td'), type),
                (select Aug as '*' for xml path('td'), type),
                (select Sep as '*' for xml path('td'), type),
                (select Oct as '*' for xml path('td'), type),
                (select Nov as '*' for xml path('td'), type),
                (select Dec as '*' for xml path('td'), type),
                (select Total as '*' for xml path('td'), type)          

        from @MyTable
        GROUP BY [StayYear], [PropertyCode], [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [Total]
        ORDER BY  [PropertyCode], [StayYear] DESC
        for xml path('tr'), type
    )

for xml path('table')
)

select @tableHtml

Output is:

However, my desired output is as follows:

What do I need to modify in my XML codes for this to happen? Basically, I want to hard code the color values for the rows associated to each set of PropertyCode (that is A, B and C).

Since I am not too conversant with HTML and XML, I'm having a hard time trying to figure out the solution.

see this post https://stackoverflow.com/questions/12161771/sp-send-dbmail-formatting-row-in-table-as-red-for-an-alert — iSR5, Feb 24 '18 at 14:31
Thanks but it's hard to understand what it is doing as the solution is not reproducible since I don't have a copy of AdventureWorks installed on my SQL Server. — user3115933, Feb 24 '18 at 14:49
In [this answer](https://stackoverflow.com/a/39487565/5089204) I provide a function which will *transform* any select into an HTML-table. With large support for classes on row and cell level, hyperlinks and CSS support. — Shnugo, Feb 25 '18 at 14:47

DatabaseCoder · Accepted Answer · 2018-02-24T14:48:56.753

0

We can add this column -

(case when [PropertyCode]='A' then 'background-color:green' when [PropertyCode]='B' then 'background-color:blue' end) as '@style'

after this column (Line 51 of SQL script) -

select 'trclass' as '@class'

I have added case only for Property code A and B. We can add same for other Property codes.

edited Feb 24 '18 at 14:48

answered Feb 24 '18 at 14:37

DatabaseCoder

2,004
2
12
22

Fantastic! Exactly what I needed. – user3115933 Feb 24 '18 at 14:57
I don't know if I need to post this as a new question but do you know how to separate the values inside the table with a comma acting as a thousand separator? – user3115933 Feb 24 '18 at 15:27

How do I modify my XML codes in this SQL Query to highlight specific rows of the table?

1 Answers1