SQL Server HTML Body Email not formatting correctly (added XML)

Question

The Story: We have a requirement to send periodic requests to customer requesting they re-certify thier profiles that are in our system. Curently this in a manual process but we have dscided to automate it by using SQL Server's email capabilites.

My solution: I'm curently working on the body of the email which will then be emailed to the customer. Required in the body are the contact information and a table with each process that needs to be recertified.

The problem: The difficulty I am encountering is that, even though I'm moving (it looks like) from top to bottom, the HTML email body I've developed is not formatting properly and I can't tell why not.

Here is my SQL code

WITH htmlresult AS (
SELECT 
''AS RowNumber 
,' 
     
' AS HtmlData

UNION ALL

select top 1
''AS RowNumber 
, 'Date:    '  + CAST(GETDATE() AS VARCHAR(12)) + '

 ' + 
  'Re: Annual Re-certification Information Profiles'  + '

 ' + 
  'Contact Name:    ' + dbo.DueTempTbl.[Contact]  + '    
Email: ' + dbo.DueTempTbl.[EMAIL] + ' 
' +
  'GenName:    ' + dbo.DueTempTbl.[GenName]+ '
 ' +
  'GenNumber:    ' + dbo.DueTempTbl.[Gen#] + '

 ' AS 
 HtmlData
FROM  dbo.DueTempTbl 
WHERE  [RECERTDUE] Between DATEADD(DAY, -180, GETDATE()) And DATEADD(DAY, 
+60, GETDATE())  
AND ((dbo.DueTempTbl.[Terr])='AZ1') AND ((LTrim(RTrim([EMAIL])))<>'')
AND [Gen#]=623113

UNION ALL

SELECT 
'' AS RowNumber 
, 'The annual re-certification of your information profiles are due for 
 those expiring 
 in the next ninety (90) days. ' + 
  'It is necessary to complete this re-certification in advance to ensure no 

 interruption of future service.  

'  AS HtmlData

union all

SELECT '' AS RowNumber 
,'  

' AS 
HtmlData

Union All     

SELECT ROW_NUMBER() OVER (ORDER BY [IDNumber]) AS RowNumber
,'' + 
 '' AS HtmlData
FROM  dbo.DueTempTbl 
WHERE  [RECERTDUE] Between DATEADD(DAY, -180, GETDATE()) And DATEADD(DAY, 
+60, GETDATE())  
AND [Gen#]=623113
SELECT HtmlData =
    CASE RowNumber%2
        WHEN 0 THEN '' + HtmlData + 
''
        ELSE '' + HtmlData + ''
    END
FROM htmlresult

UNION ALL

SELECT '
    ID Number Description Recert Due Date
' + CAST([IDNumber] as VARCHAR(10)) + ' ' + RTrim([WasteName]) 
+ ' ' + CAST([RECERTDUE]as VARCHAR(12)) + '


 Thank you for allowing us to provide you with 
reliable and safe service.  
 ' +
       'We strive for excellence in providing these services and we hope 
that this has been your 
 experience ' +
       'with us.'

Here is the output I was expecting







Date:    Sep  7 2018 


 Re: Annual Re-certification of Information Profiles


 Contact Name:    Paul Bunyan    Email: 
Bunyan@SomeCompany.com                            

Gen Name:    Some Company   

Gen Number:    623113


The annual re-certification of your 
Information Profiles are due for those expiring 
 in the next ninety (90)
 days. It is necessary to complete this process in advance to ensure no 
 
interruption of service.  





 
    
IDNumber Decription Recert Due Date
780877 quas vidit docendi pro Sep 13 2018 
780878 Lorem ipsum 
dolor Sep 13 2018 
780879 sit amet Sep 13 2018 
780880 uisset eligendi 
ius Sep 13 2018 
780881 vix illum commune Sep 13 2018 
780882 mea omnesque 
liberavisse Sep 13 2018 
780883 suscipiantur cu Sep 13 2018 
780884 cam appareat mei 
ut Sep 13 2018 
780885 sit maiorum repudiare Sep 13 2018 
780886 olum dicat 
fabellas Sep 13 2018 
780937 vidit docendi pro Sep 13 2018 



Thank you for allowing us to provide you with reliable and safe services. We 
strive for excellence in providing these services and we hope that this has 
been your 

 experience with us.

And here is what I got








Date:    Sep  7 2018


 Re: Annual Re-certification of Information Profiles


 Contact Name:    Paul Bunyan    Email: 
Bunyan@SomeCompany.com

Gen Name:    Some Company

Gen Number:    623113

        

Date:    Sep  7 2018


 Re: Annual Re-certification of Information Profiles


 Contact Name:    Paul Bunyan    Email: 
Bunyan@SomeCompany.com

Gen Name:    Some Company

Gen Number:    623113


The annual re-certification of your 
Information Profiles are due for those expiring 

 in the next ninety (90) days. It is necessary to complete this process 
in advance to ensure no 
 interruption of service.  


 

      
IDNumber Decription Recert Due Date
780877 quas vidit docendi pro Sep 13 2018 
780878 Lorem ipsum 
dolor Sep 13 2018 
780879 sit amet Sep 13 2018 
780880 uisset eligendi 
ius Sep 13 2018 
780881 vix illum commune Sep 13 2018 
780882 mea omnesque 
liberavisse Sep 13 2018 
780883 suscipiantur cu Sep 13 2018 
780884 cam appareat mei 
ut Sep 13 2018 
780885 sit maiorum repudiare Sep 13 2018 
780886 olum dicat 
fabellas Sep 13 2018 
780937 vidit docendi pro Sep 13 2018 


 Thank you for allowing us to provide you with reliable and 
safe services. We strive for excellence in providing these services and we 
hope that this has been your 
 experience with us.

(SQL Server 2008R2)

By the way, I really should give credit to Steve Moore, the source I used in development of this code. http://www.sqlservercentral.com/articles/T-SQL/99398/

Rick Savoy · Accepted Answer

Ok, XML worked! Here is the final code that I used and it is producing well formated HTML.

Declare @Body varchar(max),
      @TableHead varchar(max),
      @TableTail varchar(max),
      @Message1 varchar (max),
      @Message2 varchar (max),
      @genNum as integer,
      @HTMLHead as varchar(max),
      @TableBody varchar(max)

SET @genNum = 623113      

Set NoCount On;

SELECT TOP 1 @Message1 ='Date:    '  + CAST(GETDATE() AS VARCHAR(12)) + '

 ' + 
  'Re: Annual Re-certification of Information Profiles'  + '

 ' + 
  'Contact Name:    ' + dbo.TblRecertsDueTemp.[Contact]  + '    Email: ' + 
  dbo.TblRecertsDueTemp.[EMAIL] + ' 
 ' +
  'Generator Name:    ' + dbo.TblRecertsDueTemp.[GenName]+ '
 ' +
  'Generator Number:    ' + dbo.TblRecertsDueTemp.[Gen#] + '

 '
FROM  dbo.TblRecertsDueTemp 
WHERE  DBO.FN_ChangeToDate([RECERTDUE])  Between DATEADD(DAY, -180, GETDATE()) And DATEADD(DAY, +60, GETDATE())  
AND [Gen#]=@genNum;

SET @Message2 = @Message1 + ' The annual re-certification of your Information Profiles are due for those expiring in the next ninety (90) days. It is necessary to complete this process in advance to ensure no interruption of future services. We have a simple Web form for you to use to complete this process. Click here to see directions on how to complete these recertifications. 
 
 

 '

Set @HTMLHead = ' ' +
                  '' +
              '' +
              ''

Set @TableHead =  '' +
              '' +      
              '  '+
              '  '+
              '  '+
              '' 

Set @TableTail = 'WIP Decription Recert Due Date'; 
Select @TableBody = (SELECT ROW_NUMBER() OVER (ORDER BY [WIP])  % 2 As [TRRow],
    CAST([IP] AS VARCHAR(10)) + '   '  As [TD], 
    RTrim([ProcessName]) As [TD],
    CONVERT(varchar,DBO.FN_ChangeToDate([RECERTDUE]),101) As [TD]
FROM  dbo.TblRecertsDueTemp 
WHERE  DBO.FN_ChangeToDate([RECERTDUE])  Between DATEADD(DAY, -180, GETDATE()) And DATEADD(DAY, +60, GETDATE())  
AND [Gen#]=@genNum
For XML raw('tr'), Elements)

-- Replace the entity codes and row numbers
Set @TableBody = Replace(@TableBody, '_x0020_', space(1))
Set @TableBody = Replace(@TableBody, '_x003D_', '=')
Set @TableBody = Replace(@TableBody, '1', '')
Set @TableBody = Replace(@TableBody, '0', '')

Select @Body = @HTMLHead+ @Message2 + @TableHead + @TableBody + @TableTail

-- return output
Select @Body

(Developed with the help of Robert Davis- http://www.sqlservercentral.com/blogs/robert_davis/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML/)

SQL Server HTML Body Email not formatting correctly (added XML)

Answers (2)

Related Questions