DBmail内嵌XML表格URL无法显示为超链接的技术求助

问题描述 #

DBmail脚本内嵌XML表格整体运行正常，但其中一列动态生成的URL链接，希望在邮件中显示为文本“Go To Policy”的超链接，实际却直接显示完整的HTML代码：

<a href="https://app.powerbi.com/groups/d3db31d9-199f-4b17-b34f-77db995e3f7c/reports/63088f&experience=power-bi&filter=PolicyAnalysisQuery%2FPolicyReference%20eq%20%27MYPOLICYREFERENCE%27">Go To Policy</a>

原代码片段 #

XML生成部分 #

SET @xml = CAST((
            SELECT 
                PolicyReference AS 'td','',
                BusinessUnit AS 'td','',
                PolicyDescription AS 'td','',
                CONVERT(NVARCHAR, ExpiryDate, 103) AS 'td','',
                ExpiryDays AS 'td','',
                ClientName AS 'td','',
                -- Create the hyperlink using HTML tags
               CONCAT('<a href="', PolicyLink, '">Go To Policy</a>') AS 'td','',
                FORMAT(CAST([Policy_Outstanding_GBP] AS DECIMAL(18,2)), '#,0.00') AS 'td'
            FROM 
                (SELECT DISTINCT PolicyReference, BusinessUnit, PolicyDescription, ExpiryDate, ExpiryDays, ClientName, [Policy_Outstanding_GBP], 
                    'https://app.powerbi.com/groups/&experience=power-bi&filter=PolicyAnalysisQuery%2FPolicyReference%20eq%20%27' 
                    + PolicyReference + '%27' AS PolicyLink
                 FROM TABLE_NAME a
                 WHERE ProductClass = @BusinessUnit) AS t
            ORDER BY ExpiryDate
            FOR XML PATH('tr'), ELEMENTS
        ) AS NVARCHAR(MAX));

邮件体构造部分 #

-- Construct table for the current BusinessUnit
SET @UnitTable = '<p>' + @BusinessUnit + '</p>
        <table border="1" style="font-size: 10pt;">
        <tr><th style="background-color: #1E90FF; color: white;">Policy Ref</th><th style="background-color: #1E90FF; color: white;">Unit</th><th style="background-color: #1E90FF; color: white;">Description</th>
        <th style="background-color: #1E90FF; color: white;">Expiry Date</th><th style="background-color: #1E90FF; color: white;">Days</th><th style="background-color: #1E90FF; color: white;">Client(s)</th><th style="background-color: #1E90FF; color: white;">Policy Link</th><th style="background-color: #1E90FF; color: white;">Outstanding Premium</th></tr>' +
        @xml +
        '</table>';

问题原因 #

SQL的FOR XML PATH('tr'), ELEMENTS会自动转义HTML特殊字符（<、>、"等），导致生成的<a>标签被转义为实体编码，邮件客户端将其识别为纯文本而非可渲染的HTML元素。

解决方案 #

1. 修改XML生成逻辑，保留原始HTML #

将XML生成部分的代码调整为：

SET @xml = CAST((
    SELECT 
        PolicyReference AS 'td','',
        BusinessUnit AS 'td','',
        PolicyDescription AS 'td','',
        CONVERT(NVARCHAR, ExpiryDate, 103) AS 'td','',
        ExpiryDays AS 'td','',
        ClientName AS 'td','',
        -- 将HTML链接转为XML类型，避免自动转义
        CAST(CONCAT('<a href="', PolicyLink, '">Go To Policy</a>') AS XML) AS 'td','',
        FORMAT(CAST([Policy_Outstanding_GBP] AS DECIMAL(18,2)), '#,0.00') AS 'td'
    FROM 
        (SELECT DISTINCT PolicyReference, BusinessUnit, PolicyDescription, ExpiryDate, ExpiryDays, ClientName, [Policy_Outstanding_GBP], 
            'https://app.powerbi.com/groups/&experience=power-bi&filter=PolicyAnalysisQuery%2FPolicyReference%20eq%20%27' 
            + PolicyReference + '%27' AS PolicyLink
         FROM TABLE_NAME a
         WHERE ProductClass = @BusinessUnit) AS t
    ORDER BY ExpiryDate
    FOR XML PATH('tr'), TYPE
).value('.', 'NVARCHAR(MAX)') AS NVARCHAR(MAX));

• 核心改动：将拼接的HTML链接CAST为XML类型，并使用FOR XML PATH('tr'), TYPE生成XML，最后通过.value('.', 'NVARCHAR(MAX)')提取内容，避免特殊字符被转义。

2. 确保DBmail发送时指定HTML格式 #

调用sp_send_dbmail时必须设置@body_format = 'HTML'，否则邮件会以纯文本形式显示：

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Your_Profile_Name', -- 替换为你的邮件配置文件名称
    @recipients = 'recipient@example.com', -- 替换为收件人邮箱
    @subject = 'Policy Expiry Report',
    @body = @UnitTable,
    @body_format = 'HTML'; -- 关键：指定邮件体为HTML格式

修改效果 #

调整后，生成的邮件中该列会显示为可点击的超链接“Go To Policy”，而非原始HTML代码。

内容的提问来源于stack exchange，提问作者Marc Steel