使用FOR XML格式化含NULL值的SQL结果时行缺失如何解决

问题根因 #

SQL Server 中任意 NULL 值参与字符串拼接时，都会使整个拼接结果返回 NULL。你示例中 John 对应的 Address 字段为 NULL，拼接出的整段 <td> 内容最终为 NULL，FOR XML PATH 处理时会直接跳过该条记录，导致最终邮件里看不到这行数据。

修复方案 #

你只需要在拼接字段时，用 ISNULL 函数把所有可能为空的字段替换为空字符串即可，修改后的代码如下：

DECLARE @BODY1 NVARCHAR(MAX)
SET @BODY1 = CAST((SELECT td = ISNULL(Name,'') + '</td><td>' + ISNULL(Number,'') + '</td><td>' + ISNULL(Address,'') + '</td>'
FROM
(
   SELECT
       Name, Number, Address
   FROM
       Table1
) as Sub
FOR XML PATH('tr'), type) AS VARCHAR(MAX))

SET @BODY1 = '<TABLE CELLPADDING="3" CELLSPACING="3" BORDER="1">'+ '<TR><TH>Name</TH><TH>Number</TH><TH>Address</TH></TR>' + REPLACE(REPLACE(@Body1, '&lt;','<'), '&gt;','>') + '</TABLE>'

EXEC MSDB.DBO.SP_SEND_DBMAIL
@PROFILE_NAME = 'NAME',
@RECEPIENTS = 'NAME@DOMAIN.COM',
@BODY = @Body1,
@SUBJECT = 'Details',
@BODY_FORMAT = 'HTML',
@EXECUTE_QUERY_DATABASE = 'NAME';

更稳妥的优化写法（可选） #

如果不想手动处理HTML标签转义问题，也可以用XML原生的元素生成方式，写法更简洁也不容易出转义错误：

DECLARE @BODY1 NVARCHAR(MAX)

-- 生成表头
SET @BODY1 = N'
<TABLE CELLPADDING="3" CELLSPACING="3" BORDER="1">
  <TR>
    <TH>Name</TH>
    <TH>Number</TH>
    <TH>Address</TH>
  </TR>'

-- 生成表体，自动处理NULL和转义
SET @BODY1 += CAST((
    SELECT 
      ISNULL(Name,'') AS 'td', '',
      ISNULL(Number,'') AS 'td', '',
      ISNULL(Address,'') AS 'td'
    FROM Table1
    FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX))

SET @BODY1 += N'</TABLE>'

-- 后续发邮件逻辑不变
EXEC MSDB.DBO.SP_SEND_DBMAIL
@PROFILE_NAME = 'NAME',
@RECEPIENTS = 'NAME@DOMAIN.COM',
@BODY = @Body1,
@SUBJECT = 'Details',
@BODY_FORMAT = 'HTML',
@EXECUTE_QUERY_DATABASE = 'NAME';

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