下面的代码为我提供了嵌套在另一个表中的表所需的基本输出，请参阅屏幕截图
但是，如果不添加代码行，我就无法获得正确的输出
Set @BodyTxt =(select REPLACE(replace (@BodyTxt,'<','<'),'>','>'))
因为原始代码被转义为“<”和“>”。
有没有更干净或更好的方法来做到这一点？
请注意，使用 dB 邮件只是查看 HTML 输出的快速方法（有更好的方法吗？）。如果您使用我的代码，您需要配置 dB 邮件并更改电子邮件配置文件和地址
/***********************************************/
/*** set up test data */
/***********************************************/
DROP TABLE IF EXISTS dbo.Units
DROP TABLE IF EXISTS dbo.Document
CREATE TABLE Units(
DocNum int,
DocRow int,
Serial varchar(255),
Element varchar(255),
ManDate datetime)
INSERT dbo.Units
Values (50009,0,'Serial1','Element1','2021-01-04 00:00:00'),
(50009,0,'Serial2','Element2','2021-01-04 00:00:00'),
(50009,1,'Serial4','Element2','2021-01-06 00:00:00'),
(50006,0,'Serial3','Element2','2021-01-07 00:00:00')
CREATE TABLE Document(
DocNum int,
DocRow int,
Part varchar(255),
Qty int,
DocDate datetime)
INSERT dbo.Document
Values (50009,0,'PartA',2,'2023-07-04 00:00:00'),
(50008,0,'PartC',1,'2023-06-28 00:00:00'),
(50007,0,'PartB',4,'2023-06-24 00:00:00'),
(50006,0,'PartA',1,'2023-01-04 00:00:00')
/******* End of Test Data set up *************/
declare @BodyTxt nvarchar(max)
Set @BodyTxt =(
SELECT
(SELECT 'Table I' FOR XML PATH(''),TYPE) AS 'caption',
(SELECT 'Doc' AS th, 'Line' AS th,'Part' as th,'Qty' as th,'Unit Details' as th FOR XML raw('tr'),ELEMENTS, TYPE) AS 'thead',
--(SELECT 'sum' AS th, 'twenty' AS th FOR XML raw('tr'),ELEMENTS, TYPE) AS 'tfoot',
(SELECT DocNum AS td, DocRow AS td,Part AS td,Qty AS td, (Select (Select Serial as td,Element as td,convert (varchar ,ManDate, 6) as td from dbo.Units U Where U.DocNum=D.DocNum and U.DocRow=D.DocRow FOR XML RAW('tr'), ELEMENTS, TYPE ) AS 'tbody'
FOR XML PATH(''), ROOT('table')) AS td FROM dbo.Document D FOR XML RAW('tr'), ELEMENTS, TYPE
) AS 'tbody'
FOR XML PATH(''), ROOT('table'))
Set @BodyTxt =(select REPLACE(replace (@BodyTxt,'<','<'),'>','>'))
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Send via 365'
,@recipients = '[email protected]'
,@subject = 'Test table'
,@body = @BodyTxt
,@body_format = 'HTML';
