P粉5503233382023-09-04 00:35:48
请尝试以下解决方案。
它使用 SQL Server XQuery 功能。
此外，它还使用 CSS 来设置输出 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));
输出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>