英文:
Background or font colour red in HTML
问题
I have the below code to send email notification to recipients and good. Source data is from a SQL Server query, I temporarily call "#Temp_Warning" table. My recipients want to highlight the value of T.FORMATTED_ENTRY in red in the email if it is less than 10. I have tried a lot but the values less than 10 shows unexpectedly the whole code "5" in the email instead of only number 5 in red.
This is the part of the code of a SQL Server Stored Procedure:
IF (SELECT COUNT(*) FROM #Temp_Warning) > 0
BEGIN
--Format email content in HTML
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML =
N'<tr>' +
N'<td><b>Test Name</b></td>' +
N'<td><b>Formatted result</td>' +
N'</tr>' +
CAST((
SELECT
td = T.REPORTED_NAME,'',
td = CASE WHEN T.FORMATTED_ENTRY < 10 THEN N'<span style="background-color:red;">' + T.FORMATTED_ENTRY + N'</span>' ELSE T.FORMATTED_ENTRY END,''
FROM #Temp_Warning T
ORDER BY T.REPORTED_NAME
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
END
英文:
I have the below code to send email notification to recipients and good. Source data is from a SQL Server query, I temporarily call "#Temp_Warning" table. My recipients want to highlight the value of T.FORMATTED_ENTRY in red in the email if it is less than 10. I have tried a lot but the values less than 10 shows unexpectedly the whole code "span style="background-color:red;">5</span" in the email instead of only number 5 in red.
This is the part of the code of a SQL Server Stored Procedure
IF (SELECT COUNT(*) FROM #Temp_Warning) > 0
BEGIN
--Format email content in HTML
DECLARE @tableHTML NVARCHAR(MAX);
SET @tableHTML =
N'<tr>' +
N'<td><b>Test Name</b></td>' +
N'<td><b>Formatted result</td>' +
N'</tr>' +
CAST((
SELECT
td = T.REPORTED_NAME,'',
td = CASE WHEN T.FORMATTED_ENTRY < 10 THEN N'<span style="background-color:red;">' + T.FORMATTED_ENTRY + N'</span>' ELSE T.FORMATTED_ENTRY END,''
FROM #Temp_Warning T
ORDER BY T.REPORTED_NAME
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
END
答案1
得分: 1
以下是翻译好的内容：
请尝试以下解决方案。
它使用了SQL Server XQuery 功能。
此外，它使用CSS来样式化输出的XHTML。
SQL
-- DDL和示例数据填充，开始
DECLARE @tbl TABLE (test VARCHAR(100) PRIMARY KEY, result INT);
INSERT @tbl (test, result) VALUES
('Bu', 57),
('Po', 5),
('Zu', 9);
-- DDL和示例数据填充，结束
DECLARE @xhtmlBody XML
, @body NVARCHAR(MAX)
, @tableCaption VARCHAR(30) = '测试结果报告';
SET @xhtmlBody = (SELECT (
SELECT * FROM @tbl FOR XML PATH('row'), TYPE, ROOT('root'))
.query('
| 测试名称 | 格式化结果 |
|---|---|
| {data($row/test)} |
{if (($row/result/text())[1] lt 10) then attribute id {"red"} else ()} {data($row/result)} |
'));
SELECT @xhtmlBody;
SET @body = TRY_CAST(@xhtmlBody AS NVARCHAR(MAX));
输出的XHTML
| 测试名称 | 格式化结果 |
|---|---|
| Bu | 57 |
| Po | 5 |
| Zu | 9 |
英文:
Please try the following solution.
It is using SQL Server XQuery powers.
Additionally, it is using CSS for styling output XHTML.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (test VARCHAR(100) PRIMARY KEY, result INT);
INSERT @tbl (test, result) VALUES
('Bu', 57),
('Po', 5),
('Zu', 9);
-- DDL and sample data population, end
DECLARE @xhtmlBody XML
, @body NVARCHAR(MAX)
, @tableCaption VARCHAR(30) = 'Tests results report';
SET @xhtmlBody = (SELECT (
SELECT * FROM @tbl 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: 300px;} ]]>
th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
th, td <![CDATA[ { text-align: left; padding: 8px;} ]]>
tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
td:nth-child(2) {text-align: center;}
#red <![CDATA[ {background-color: red;} ]]>
</style>
</head>
<body>
<table border="1">
<caption><h2>{sql:variable("@tableCaption")}</h2></caption>
<thead>
<tr>
<th>Test Name</th>
<th>Formatted result</th>
</tr>
</thead>
<tbody>
{
for $row in /root/row
return <tr>
<td>{data($row/test)}</td>
<td>
{if (($row/result/text())[1] lt 10) then attribute id {"red"} else ()}
{data($row/result)}
</td>
</tr>
}
</tbody></table></body></html>'));
SELECT @xhtmlBody;
SET @body = TRY_CAST(@xhtmlBody AS NVARCHAR(MAX));
Output XHTML
<html>
<head>
<meta charset="utf-8" />
<style>
table {border-collapse: collapse; width: 300px;}
th {background-color: #4CAF50; color: white;}
th, td { text-align: left; padding: 8px;}
tr:nth-child(even) {background-color: #f2f2f2;}
td:nth-child(2) {text-align: center;}
#red {background-color: red;}
</style>
</head>
<body>
<table border="1">
<caption>
<h2>Tests results report</h2>
</caption>
<thead>
<tr>
<th>Test Name</th>
<th>Formatted result</th>
</tr>
</thead>
<tbody>
<tr>
<td>Bu</td>
<td>57</td>
</tr>
<tr>
<td>Po</td>
<td id="red">5</td>
</tr>
<tr>
<td>Zu</td>
<td id="red">9</td>
</tr>
</tbody>
</table>
</body>
</html>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论