我不知道如果这能帮助你,但...
曾几何时,我需要发送的查询结果作为电子邮件正文中的HTML表格。我为自己准备了一个可能不是最好的脚本,但是您不必为了使其适用于任何需要的查询而进行更改。
只是想不到,如果存在查询必须是相同的,你的实际查询的HTML表,那抽象查询列表标题必须有相同数量的列,您的查询(这是跛脚,我知道。可能有查询到临时表,并要求信息架构的列名,但我很满意我所得到的,不想把钱花在这更多的时间)
declare @table xml
if exists (select top 3 status,
name,
createdate
from sys.sysusers
where altuid = 1)
begin
--CAUTION!
--query that creates table headers and query that creates content must have the same number of fields
--create table with data
set @table = (--table formating
select '1' as '@border',
'3' as '@cellpadding',
'0' as '@cellspacing',
(--table header formating
select 'center' as '@align',
'font-weight: bold; background-color:silver' as '@style',
(--abstract entry with table headers
select 1 as Tag,
null as Parent,
'status' as [tr!1!td!element],
'name' as [tr!1!td!element],
'createdate' as [tr!1!td!element]
for xml explicit,
type
)
for xml path('tbody'),
type
),
(--actual query
select top 3
1 as Tag,
null as Parent,
status as [tr!1!td!element],
name as [tr!1!td!element],
convert(nvarchar(19), createdate, 120) as [tr!1!td!element]
from sys.sysusers
where altuid = 1
for xml explicit,
type
)
for xml path('table')
)
end
select @table
,会给你像下面不错的HTML表脚本
status | name | createdate |
0 | public | 2003-04-08 09:10:42 |
0 | db_owner | 2003-04-08 09:10:42 |
0 | db_accessadmin | 2003-04-08 09:10:42 |
您可以更改@table type fr om xml to varchar,然后只需添加一些额外的操作和关闭您需要的html标签。
保重