|
Оформление результата запроса в виде таблицы при отправке письма
#39641384
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
declare @query nvarchar(1000)='select top 20 * from master..spt_values'
declare @xml xml
declare @HTML nvarchar(max)
declare @StartTime datetime=getdate()
--
set @query=N'set @xml=('+@query+N' for xml raw, elements xsinil, type, xmlschema)'
exec sp_executesql @query, N'@xml xml out', @xml=@xml out
set @HTML =
N'<html>' +
N'<head>'+
N' <style type="text/css">'+
N' th {background-color: #03A2B0;}'+
N' table {border-collapse: collapse; padding: 5px;}'+
N' table, th, td {border: 1px solid black;}'+
N' .tr_class0 {background-color: #CDF3F7;}'+
N' </style>'+
N'</head>'+
N'<body>' +
N'<h3>Прувэт !!1</h3>'+
N'<table border="1">'+[dbo].[fnRawXMLToHTML](@xml,null)+N'</table>'+
N'< br /><font size="2" color="#778899" face="Arial"><i>Покеда !</i></font>' +
N'< br />' +
N'<font size="1" color="#778899" face="Arial"><i>Время формирования: '+cast(datediff(ms, @StartTime, getdate()) as varchar)+' ms</i></font>' +
N'</body>' +
N'</html>';
--print @HTML
exec msdb.dbo.sp_send_dbmail
@profile_name='My profile name'
,@recipients='billy@microsoft.com'
,@subject='My subject'
,@body=@HTML
,@body_format='HTML'
<br />в выделенных строках поправить нужно, а то форум их "съедает" если правильно написать ...
fnRawXMLToHTML 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
create FUNCTION [dbo].[fnRawXMLToHTML](
@xml xml -- xml-данные в формате raw
,@th_bgcolor varchar(20)=null -- Цвет строки с подписями
)
RETURNS nvarchar(max)
AS
BEGIN
declare @html nvarchar(max)
declare @th xml, @td xml
declare @MoneyFormat nvarchar(20)='###'+char(160)+'###'+char(160)+'##0.00'
declare @xmlschema table (FieldName nvarchar(100), FieldType nvarchar(100))
-- Если задана схема
insert into @xmlschema
(FieldName, FieldType)
select
x.z.value('@name', 'varchar(100)') as q
,isnull(x.z.value('@type', 'nvarchar(100)'),x1.z1.value('@base', 'nvarchar(100)')) as q1
from
@xml.nodes('*/*/*/*/*:element') as x(z)
outer apply x.z.nodes('*:simpleType/*:restriction') as x1(z1)
-- Подписи
set @th=
(select
@th_bgcolor as 'tr/@bgcolor'
,(select replace(t.c.value('local-name(.)', 'nvarchar(100)'),'_',' ')
from @xml.nodes('/*:row[1]/*') as t(c)
where t.c.value('local-name(.)', 'nvarchar(100)')<>'trclass'
for xml path('th'), type) as tr
for xml path(''))
-- Данные
set @td=
(select
case when a.OneRow.value('./*:trclass[1]/text()[1]', 'int') is null
then case when row_number()over(order by (select 1)) % 2=1 then 'tr_class1' else 'tr_class0' end
else 'tr_class'+cast(a.OneRow.value('./*:trclass[1]/text()[1]', 'int') as varchar)
end as 'tr/@class'
,(select
case
when a.FieldType in ('sqltypes:int', 'sqltypes:numeric', 'sqltypes:smallint', 'sqltypes:tinyint', 'sqltypes:money') then 'right'
when a.FieldType in ('sqltypes:datetime') then 'center'
else null
end as '@align'
,
case
when a.FieldType in ('sqltypes:money')
then Format(t1.c.value('./text()[1]', 'money'), @MoneyFormat)
when a.FieldType in ('sqltypes:datetime')
then Format(t1.c.value('./text()[1]', 'datetime'), 'dd\.MM\.yyyy')
else t1.c.value('./text()[1]', 'nvarchar(1000)')
end
from a.OneRow.nodes('/*') as t1(c)
outer apply (
select
x.FieldType
from @xmlschema x
where x.FieldName=t1.c.value('local-name(.)', 'nvarchar(100)')
) a
where t1.c.value('local-name(.)', 'nvarchar(100)')<>'trclass'
for xml path('td'), elements, type) as tr
from
(select
t.c.query('./*') as OneRow
from @xml.nodes('/*:row') as t(c) ) a
for xml path(''))
-- "Склейка" результату (char(10) ==> < br />)
set @html = isnull(cast(@th as nvarchar(max)), '')+isnull(replace(cast(@td as nvarchar(max)),char(10),'< br />'), '')
--
return @html
END
|
|
|
