Data Monitor XML & HTML Email
***** My Valid Data Monitor **** XML OUT *** HTML EMAIL DELIVERY *****
IF OBJECT_ID('tempdb..#tCustomerID') IS NOT NULL DROP TABLE #tCustomerID
select distinct
mm.CustomerID
Into #tCustomerID
from myDatabase..myTable (nolock) mm
left join myDatabase.dbo.myOtherTable (nolock) mo
on mm.refID = mo.id
left join myDatabase.dbo.myOtherTableDetail (nolock) mod
on mo.id = mod.myOtherTableid
and mm.SearchValue = mod.SearchValue
where mm.refID is not null
and mm.quantity is not null
and mod.SearchValue is null
IF Exists (Select 1 From #tCustomerID)
BEGIN
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [CustomerID] AS 'td' FROM #tCustomerID ORDER BY CustomerID
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>My Valid Data Monitor</H3>
<table border = 1><tr> <th> CustomerID </th> </tr>'
SET @body = @body + @xml +'</table></body></html>'
--SELECT @body
exec msdb.dbo.sp_send_dbmail
@profile_name = 'dbmailProfile'
,@recipients = 'usergroup@mydomain.com'
,@subject = 'My Valid Data Monitor'
,@body = @body
,@body_format = 'HTML'
END
