1

I'm trying to run a query in SQL Server to generate a data in tabular format and add html tags so that when I write the output of this into mail it should generate the data in tabular format. The HTML generated seems to work correctly when I save it into a .html file and open it in browser. But it just shows as a plain text with html code in the mail. Please help!!

Below is the function I created in SQL Server. I'm sending the output of this to be displayed in email using Markit EDM tool.

CREATE FUNCTION dbo.<Function_Name> ()
RETURNS VARCHAR(max)
AS   
BEGIN
    DECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)

    SET @xml = CAST((SELECT <Column1> AS 'td','', <Column2> AS 'td', '', <Column3> AS 'td'
                     FROM <table Name>
                     FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

    SET @body ='<html><body><H3>Contact Info</H3>
                <table border =1>
                <tr>
                   <th> Solution Name </th> 
                   <th> Start Time </th> 
                   <th> Run Time </th></tr>'    

    SET @body = @body + @xml +'</table></body></html>'

    RETURN @Body
END

Expected result (in tabular format)

<html><body><H3>Contact Info</H3>
<table border=1>
<tr>
<th> Solution Name </th>
 <th> Start Time </th> 
 <th> Run Start </th>
</tr>
 <tr>
 <td>Test maxrun Solution</td>
 <td>2019-01-11T11:58:48.077</td>
 <td>00:00:00</td>
 </tr>
 <tr>
 <td>Test maxrun Solution1</td>
 <td>2019-01-09T10:12:21.643</td>
 <td>01:46:26</td>
 </tr>
 <tr>
 <td>Test maxrun Solution2</td>
 <td>2018-12-28T14:50:16.270</td>
 <td>21:08:32</td>
 </tr>
 </table>
 </body>
 </html>

Actual result in the mail just displays plain html text instead of the table.

<html><body><H3>Contact Info</H3>
<table border=1>
<tr>
<th> Solution Name </th>
 <th> Start Time </th> 
 <th> Run Start </th>
</tr>
 <tr>
 <td>Test maxrun Solution</td>
 <td>2019-01-11T11:58:48.077</td>
 <td>00:00:00</td>
 </tr>
 <tr>
 <td>Test maxrun Solution1</td>
 <td>2019-01-09T10:12:21.643</td>
 <td>01:46:26</td>
 </tr>
 <tr>
 <td>Test maxrun Solution2</td>
 <td>2018-12-28T14:50:16.270</td>
 <td>21:08:32</td>
 </tr>
 </table>
 </body>
 </html>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kiko
  • 11
  • 1
  • Please be ware that table border attribute is obsolete since HTML4. – Petro K Jan 11 '19 at 21:29
  • Possible duplicate of [Create HTML Table with SQL FOR XML](https://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml) but I would use [this answer](https://stackoverflow.com/a/39487565/6167855) specifically – S3S Jan 11 '19 at 21:32
  • Take a peek at https://dba.stackexchange.com/questions/83776/need-to-send-a-formatted-html-email-via-database-mail-in-sql-server-2008-r2 Is @body_format = 'HTML' ? – John Cappelletti Jan 11 '19 at 21:33
  • That's how I used to do it @JohnCappelletti and got tired of statically entering that everytime – S3S Jan 11 '19 at 21:37
  • Thank you ! I'll take a look. – Kiko Jan 11 '19 at 21:57
  • The problem is that it is generating the html code properly.. but it shows as html code instead of the table in email. But when i copy the email contents in to a .html file.. it displayed correctly in table format in Browser. – Kiko Jan 12 '19 at 05:13

0 Answers0