日常我们发送邮件时,在主体中把数据格式以HTML Table样式显示给用户。现在我们来简单介绍FOR XML PATH 用法,查询结果集形成XML文件(HTML格式),如下
相关代码,如下
邮件发生参数
SELECT @SignCount=COUNT(1) FROM dbo.RSN_Absenteeism_Form WHERE CurrentStatus='5' AND DATEDIFF(day,SignFinishDayID,GETDATE())=1
SET @MailSubject='[RSN]昨日申請簽合成功'+@SignCount+'筆'
SET @MailFormat='HTML'
SET @MailTo='xxxx@ch.corpnet'
SET @MailCc='xxxx@casetekcorp.com'
SET @MailBcc='xxxxx@casetekcorp.com'
SET @MailBody =
N'Dear Sir/Madam:<br><H4> 列表如下:</H4>' +
N'<table border="1" style="font-size:11px;text-align:center" width="60%">' +
N'<tr style="font-size:11"><th>工號</th>' +
N'<th>姓名</th>' +
N'<th>部門ID</th>' +
CAST ( ( SELECT td = EmpID, '',
td = EmpName, '',
td = DeptID, ''
FROM dbo.RSN_Absenteeism_Form
WHERE CurrentStatus='5' AND DATEDIFF(day,SignFinishDayID,GETDATE())=1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
N'</table><br>人力資源部'