Set different bgcolor in using XML in stored procedure

Question

The data read from database

SET @xml = CAST(( 
    SELECT
      (row_number() over ( order by [LastCount] ASC)) as 'td','',
      case when [LastCount] = '' then '0' else isnull([LastCount],0) end as 'td','',
      [Line] as 'td',''
      [LastDateTime] as 'td',''
      FROM [Testing].[dbo].[InfomationTable]
      ORDER BY [LastCount] ASC
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

For setting style is

SET @style = +N'<style type="text/css">' + N'.tg  {border-collapse:collapse;border-spacing:0;border-color:#aaa;}'
    + N'.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}'
    + N'.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:700;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:'+@tdHColor+';background-color:'+@bgColor+';text-align:left}'
    + N'.tg .tg-9ajh{font-weight:bold;background-color:#68cbd0}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}'
    + N'</style>';

the output will show like this

enter image description here

but I want to show output like this

enter image description here

First this is really a CSS/HTML question, secondly you need to show the HTML which the query produces in order to know how to style it. — Dale K, Dec 04 '19 at 01:19
What about the `@tdHColor` and `@bgColor` variables - are they static, or their values should somehow differ from row to row? — Roger Wolf, Dec 04 '19 at 02:06
@RogerWolf for `@tdHColor` and `@bgColor` variables I already set to variable in sql server `set @bgColor = '#8a8a8a' set @tdHColor ='#fff'` — nazrin ahmad, Dec 04 '19 at 02:29
[In this answer](https://stackoverflow.com/a/39487565/5089204) you will find a function accepting *any SELECT* as input and returning a HTML-table with wide CSS support. You will have to learn a bit about `XML PATH`-mode in order to see the naming of controlling attributes, but this is fairly easy... — Shnugo, Dec 04 '19 at 07:33

score 0 · Answer 1 · answered Dec 04 '19 at 04:23

Here is a full example on how to generate XHTML with embedded CSS in T-SQL. It is using XQuery FLWOR expression. You could easily adjust it for your needs.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
   spid INT PRIMARY KEY
   , blocking_spid INT
   , Duration TIME
   , [database_name] VARCHAR(100)
   , open_tran_count INT
   , [login_name] VARCHAR(100)
   , [host_name]  VARCHAR(100)
   , [program_name] VARCHAR(100)
   , sql_text VARCHAR(2048) NOT NULL
);
INSERT INTO @tbl
VALUES (1, 0, '12:34:54.1237', 'DB1', 0, 'Login1', 'HostName1', 'Prog1', 'SELECT * FROM tbl;')
    , (25, 0, '10:34:54.1037', 'DB2', 0, 'Login2', 'HostName2', 'Prog2', 'SELECT * FROM AnotherTbl;')
-- DDL and sample data population, end

DECLARE @xhtmlBody XML
    , @body NVARCHAR(MAX);

SET @xhtmlBody = (SELECT (SELECT TOP(10) *
FROM @tbl 
--WHERE ...
ORDER BY spid DESC
FOR XML PATH('row'), TYPE, ROOT('root'))
.query('<html><head>
            <meta charset="utf-8"/>
            (: including embedded CSS styling :)
            <style>table <![CDATA[ { border-collapse: collapse;  width: 100%;} ]]>
                th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
                th, td <![CDATA[ { text-align: left; padding: 8px;} ]]>
                tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
            </style>
         </head>
         <body>
         <div>Report Name such and such</div>
         <div>Total SPID count: {count(/root/row)}</div>
<table border="1"><thead>
   <tr>
      <th>SPID</th>
      <th>Blocking SPID</th>
      <th>Duration days hr:mi:ss.ms</th>
      <th>Database Name</th>
      <th>Open_tran_count</th>
      <th>Login name</th>
      <th>Host name</th>
      <th>Program</th>
      <th>SQL Text</th>
   </tr></thead>
   <tbody>
{
    for $row in /root/row
    return <tr>
            <td>{data($row/spid)}</td>
            <td>{data($row/blocking_spid)}</td>
            <td>{data($row/Duration)}</td>
            <td>{data($row/database_name)}</td>
            <td>{data($row/open_tran_count)}</td>
            <td>{data($row/login_name)}</td>
            <td>{data($row/host_name)}</td>
            <td>{data($row/program_name)}</td>
            <td>{data($row/sql_text)}</td>
        </tr>
}
</tbody></table></body></html>'));

SELECT @xhtmlBody;

SET @body = CAST(@xhtmlBody AS NVARCHAR(MAX));

Output

<html>
  <head>
    <meta charset="utf-8" />
    <style>table  { border-collapse: collapse;  width: 100%;} 
                th  {background-color: #4CAF50; color: white;} 
                th, td  { text-align: left; padding: 8px;} 
                tr:nth-child(even)  {background-color: #f2f2f2;} 
            </style>
  </head>
  <body>
    <div>Report Name such and such</div>
    <div>Total SPID count: 2</div>
    <table border="1">
      <thead>
        <tr>
          <th>SPID</th>
          <th>Blocking SPID</th>
          <th>Duration days hr:mi:ss.ms</th>
          <th>Database Name</th>
          <th>Open_tran_count</th>
          <th>Login name</th>
          <th>Host name</th>
          <th>Program</th>
          <th>SQL Text</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>25</td>
          <td>0</td>
          <td>10:34:54.1037000</td>
          <td>DB2</td>
          <td>0</td>
          <td>Login2</td>
          <td>HostName2</td>
          <td>Prog2</td>
          <td>SELECT * FROM AnotherTbl;</td>
        </tr>
        <tr>
          <td>1</td>
          <td>0</td>
          <td>12:34:54.1237000</td>
          <td>DB1</td>
          <td>0</td>
          <td>Login1</td>
          <td>HostName1</td>
          <td>Prog1</td>
          <td>SELECT * FROM tbl;</td>
        </tr>
      </tbody>
    </table>
  </body>
</html>

You might want to read this [fully generic FLWOR-based approach](https://stackoverflow.com/a/39487565/5089204) — Shnugo, Dec 04 '19 at 07:31

Set different bgcolor in using XML in stored procedure

1 Answers1