提问人：Sylvia Manojlovic 提问时间：9/6/2022 最后编辑：Sylvia Manojlovic 更新时间：9/7/2022 访问量：242
将 SQL 用于 XML 和嵌套表宽度
Using SQL for XML and Nested Table width
问：
下面的代码将创建一个表格，插入测试数据，然后格式化为在电子邮件中以 html 格式发送。需要在 SQL 中设置配置文件名称，并且需要将收件人编辑为有效的电子邮件地址。生成的电子邮件如下所示。
create table #results ( docno varchar(50), customer varchar(250), telephone varchar(50),
docamount money, operator varchar(250), skuno varchar(50), descr varchar(500),
quantity int, itemvalue money, batchlineno int, paymethod varchar(50))
insert into #results values ('ORDER1', 'Mickey Mouse', '12345678', 500, 'Joe Bloggs', 'ITEM-1', 'Item Description 1', 1, 300, 1, 'Credit Card')
insert into #results values ('ORDER1', 'Mickey Mouse', '12345678', 500, 'Joe Bloggs', 'ITEM-2', 'Item Description 1', 1, 200, 2, 'Credit Card')
insert into #results values ('ORDER2', 'Mary Poppins', '9008007', 300, 'Bob Marley', 'ITEM-1', 'Item Description 1', 1, 300, 1, 'Credit Card')
declare @body varchar(max) , @docno varchar(50)
select @body =
'<html>' +
'<head>'+
'<style> .inkclass {text-align: right;} </style>' +
'</head>'+
'<body>'+
'<h3>' + 'Report' + '</h3>'
if exists(select * from #results)
begin
declare cur cursor for select distinct docno from #results order by docno
open cur
fetch cur into @docno
while @@fetch_status = 0
begin
select @body +=
'<table border="5px" >' +
'<tr>'+
'<th>Doc. No.</th>'+
'<th>Customer</th>'+
'<th>Contact No.</th>'+
'<th>Staff Member</th>'+
'<th>Doc. Amount</th>'+
'<th>Pay Method</th>'+
'</tr>'
select @body = @Body + cast(( select
convert(varchar(80), docno ) as 'td','',
convert(varchar(80), customer ) as 'td','',
convert(varchar(80), telephone ) as 'td','',
convert(varchar(80), operator ) as 'td','',
'text-align:right' as 'td/@style',convert(varchar(15), FORMAT(docamount , 'N', 'en-us') ) as 'td','',
convert(varchar(80),paymethod ) as 'td',''
from #results where batchlineno = 1 and docno = @docno
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) )
select @body += ''
select @body += '<table border="5px" >' +
'<tr>'+
'<th>Skuno</th>'+
'<th>Description</th>'+
'<th>Qty</th>'+
'<th>Line Total</th>'+
'</tr>'
select @body = @Body + cast(( select
convert(varchar(80),skuno ) as 'td','',
convert(varchar(80),descr ) as 'td','',
'text-align:right' as 'td/@style',convert(varchar(15),FORMAT(quantity , 'N', 'en-us')) as 'td','',
'text-align:right' as 'td/@style',convert(varchar(15),FORMAT(itemvalue , 'N', 'en-us')) as 'td',''
from #results where docno = @docno order by batchlineno
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) )
select @body += '</table></table><br><br>'
fetch cur into @docno
end
close cur
deallocate cur
end
select @body += '</body>' + '</html>'
if exists(select * from #results)
begin
exec msdb..sp_send_dbmail @profile_name = 'MailProfileNameInSQL' ,
@recipients = '[email protected]' , @subject = 'Transaction Detail Report',
@body = @body, @body_format = 'html'
end
drop table #results
产生的输出：
相反，我想跨越嵌套表列以占据父表的整个宽度。这在html中可能很容易做到，但是如何使用sql for XML选项来做到这一点呢？它应该看起来像这样（红色箭头显示嵌套表列应填充该区域）
答：
0赞
Charlieface
9/7/2022
#1
首先，我要说的是，SQL Server 是构造 HTML 的错误位置。它只能理解 XML，因此您必须将自己限制为 XHTML 才能兼容。
理想情况下，您应该使用某种脚本或客户端语言（如 C#、Python 或 Powershell）执行此操作。例如，可以在 SQL Server 代理中创建要按计划运行的 Powershell 作业。
尽管如此：
- 您需要的属性是 。
colspan="100" - 您希望它位于外部表单元格中。
- 你可能应该改用，但我没有为此烦恼。
<div> - 你不需要那个可怕的光标代码。你可以只使用和一些嵌套来获得你想要的结果。
STRING_AGGCROSS APPLYFOR XML - 请注意我使用 创建每一行的方式，然后使用 进行聚合。如果你只有一张大桌子，那么一个人就可以了。
FOR XMLSTRING_AGGFPR XML
WITH innerTables AS (
SELECT
r.docno,
r.customer,
r.telephone,
r.operator,
r.paymethod,
r.docamount,
r.itemvalue,
innerTable =
'<table border="5px" >' +
'<tr>'+
'<th>Skuno</th>'+
'<th>Description</th>'+
'<th>Qty</th>'+
'<th>Line Total</th>'+
'</tr>'+
STRING_AGG(x.innerTable, '') WITHIN GROUP (ORDER BY r.batchlineno)+
'</table></table><br/><br/>'
FROM #results r
CROSS APPLY (
SELECT
v.*
FROM (VALUES
(null, r.skuno),
(null, r.descr),
('text-align:right', FORMAT(r.quantity, 'N', 'en-us')),
('text-align:right', FORMAT(r.itemvalue, 'N', 'en-us'))
) v([@style], [text()])
FOR XML PATH('td'), ROOT('tr')
) x(innerTable)
GROUP BY
r.docno,
r.customer,
r.telephone,
r.operator,
r.paymethod,
r.docamount,
r.itemvalue
)
SELECT @body += STRING_AGG(
'<table border="5px" >'+
'<tr>'+
'<th>Doc. No.</th>'+
'<th>Customer</th>'+
'<th>Contact No.</th>'+
'<th>Staff Member</th>'+
'<th>Doc. Amount</th>'+
'<th>Pay Method</th>'+
'</tr>'+
x.outerTable+
'<tr><td colspan="100">'+
r.innerTable+
'</td></tr>'+
'</table></table><br/><br/>',
'')
FROM innerTables r
CROSS APPLY (
SELECT
v.*
FROM (VALUES
(null, r.docno),
(null, r.customer),
(null, r.telephone),
(null, r.operator),
(null, r.paymethod),
('text-align:right', FORMAT(r.docamount, 'N', 'en-us')),
('text-align:right', FORMAT(r.itemvalue, 'N', 'en-us'))
) v([@style], [node()])
FOR XML PATH('td'), ROOT('tr')
) x(outerTable);
评论
colspan