Changing font color and make it bold in sql by using XML

Question

-----HTML Table headers go her--------

CAST (
select = td Table description
select = td Count Row
select = case when date(...Date variable goes here) >=18 then 'Access denied' 
else 'Access verified'

How do I change font and make it bold for only 'Access denied' in t-sql(cast statement) by using xml?

This question is very vague, you need to post more than the above, that includes what seems to be the idea of a SQL query, but isn't a complete one. But, to format the html is a table generated by SQL what you would with any html, use the correct tags. For example `` is bold. — Thom A, Feb 01 '18 at 19:18
This is an HTML question, not XML. XML has no concept of bold in and of itself without a stylesheet or something that consumes the XML data and formats it. If you want to do this in SQL you need to generate valid HTML. Use the `` tag or the `` tag. — squillman, Feb 01 '18 at 19:23
Font color is a display issue. This should not be done in the database. — Sean Lange, Feb 01 '18 at 19:27
@SeanLange In general you are right about this being a display issue and I agree. Sometimes doing this in the database is valid, though. When sending HTML formatted mail, for example. — squillman, Feb 01 '18 at 19:49
@squillman you are correct. I just don't use sql to send emails directly. I always use a service for that instead. But your example is spot on. — Sean Lange, Feb 01 '18 at 19:53
[In this answer](https://stackoverflow.com/a/39487565/5089204) I provide a function, which will create a fully blown HTML table out of any `SELECT` statement. It supports CSS classes, hyperlinks and the table header. If you just want to embedd tags like `` you'll have to create these portions as XML (in your case: `XHTML`) fragments and put them to the right place. — Shnugo, Feb 02 '18 at 08:14

dummmmy · Answer 1 · 2018-02-01T20:12:26.717

reference: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96536

just checked and see somebody had the same issue.. I need to change 'customer' font and make it bold for below example

SET @tableHTML =
N'<H1>Status Brief Report</H1>' +
N'<b>This is what I have so far. I will start working on the formatting.<b>' 
+ 
 N'<table border="1">' +
N'<tr><th>Customer</th>' + 
N'<th>Bank</th>' +
N'<th>Safe</th>' +
N'<th>Enabled</th>' +  
N'<th>Poll Time</th>' +
N'<th>Data In CPR</th>' +
N'<th>Edge Matches CPR</th></tr>' +
CAST ( ( SELECT td = Customer, '',
td = Bank, '',
FROM #tempBrief
ORDER BY Customer, Bank, Safe
FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>' ;

score 0 · Answer 2 · answered Feb 02 '18 at 08:57

With the function I mentioned in my comment below your question you might go this way:

USE master;
GO
CREATE DATABASE testDB; --test purpose
GO
USE testDB;
GO
CREATE FUNCTION dbo.CreateHTMLTable
(
    @SelectForXmlRawElementsXsinil XML
   ,@tblClass VARCHAR(100)
   ,@thClass VARCHAR(100)
   ,@tbClass VARCHAR(100)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

RETURN
REPLACE(CAST(
(
    SELECT @tblClass AS [@class]  
    ,@thClass AS [thead/@class]
    ,@SelectForXmlRawElementsXsinil.query('let $first:=/row[1]
                return 
                <tr> 
                {
                for $th in $first/*
                return <th>{local-name($th)}</th>
                }
                </tr>') AS thead
    ,@tbClass AS [tbody/@class]
    ,@SelectForXmlRawElementsXsinil.query('for $tr in /row
                 return 
                 <tr>
                 {
                 for $td in $tr/*
                 return <td>{string($td)}</td>
                 }
                 </tr>') AS tbody
    FOR XML PATH('table'),TYPE
) AS NVARCHAR(MAX)),'_x0020_',' '); --blanks in column headers
END
GO

--This is the actual query

SELECT 
N'<H1>Status Brief Report</H1>' +
N'<b>This is what I have so far. I will start working on the formatting.<b>' 
+ dbo.CreateHTMLTable(
(
    SELECT 'The customer' AS [Customer]
          ,'The bank' AS [Bank]
          ,'Some safe' AS [Safe]
          ,'yes' AS [Enabled]
          ,CONVERT(VARCHAR(19),GETDATE(),121) AS [Poll Time]
          ,'yes' AS [DAta In CPR]
          ,'yes' AS [Edge Matches CPR]  
    FOR XML RAW, ELEMENTS XSINIL 
)
,NULL,NULL,NULL);

GO

--clean up

USE master;
GO
DROP DATABASE testDB;
GO

The result

<H1>Status Brief Report</H1>
<b>This is what I have so far. I will start working on the formatting.<b>
<table>
  <thead>
    <tr><th>Customer</th><th>Bank</th><th>Safe</th><th>Enabled</th><th>Poll Time</th><th>DAta In CPR</th><th>Edge Matches CPR</th></tr>
  </thead>
  <tbody>
    <tr><td>The customer</td><td>The bank</td><td>Some safe</td><td>yes</td><td>2018-02-02 09:52:09</td><td>yes</td><td>yes</td></tr>
  </tbody>
</table>

You can use this together with a css-stlye like here:

<html>
<style type="text/css" media="screen,print">
    table
    {
        color: black;
        font: arial;
        border: 1px solid black;
        border-collapse: collapse;
    }
    tr,th,td
    {
        border: 1px solid black;
    }
</style>
<body>
<!--Your table here-->
</body>
</html>

The final page would be something like this (click "run the code")

<html>
<style type="text/css" media="screen,print">
    table
    {
        color: black;
  font: arial;
  border: 1px solid black;
  border-collapse: collapse;
    }
 tr,th,td
 {
  border: 1px solid black;
 }
</style>
<body>
<H1>Status Brief Report</H1><b>This is what I have so far. I will start working on the formatting.<b><table><thead><tr><th>Customer</th><th>Bank</th><th>Safe</th><th>Enabled</th><th>Poll Time</th><th>DAta In CPR</th><th>Edge Matches CPR</th></tr></thead><tbody><tr><td>The customer</td><td>The bank</td><td>Some safe</td><td>yes</td><td>2018-02-02 09:52:09</td><td>yes</td><td>yes</td></tr></tbody></table>
</html>

Changing font color and make it bold in sql by using XML

2 Answers2