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