Inserting Image to HTML table within SQL Server stored procedure for use with DBMail

Question

I want to add an icon/image within a column of a table which is populated with SQL Server table data & the resulting emailed out.

As it stands all I'm getting in the email is

<img src="cid:RedTL.gif"/>

My code:

--Full path set within attachments
@file_attachments='E:\RedTL.gif',

SET  @TBLHTML=
        N'<STYLE type="text/css">' +
        N'.Table { background-color:#D8E7FB;border-collapse:separate;color:#000;font-size:18px; }' +
        N'.Table th { background-color:#0E0355;color:white; }' +
        N'.Table td, .Table th { padding:5px;border:0; }' +
        N'.Table td { border: 1px dotted white; }' +
        N'</STYLE>' +
        N'<table class="Table">' +
        N'<th><font face="calibri" size="2">Column1</th>' +
        N'<th><font face="calibri" size="2">Image Column</font></th>' +
        N'<th><font face="calibri" size="2">Column3</font></th>' +
        N'<th><font face="calibri" size="2">Column4</font></th>' +
        N'<th><font face="calibri" size="2">Column5</font></th>' +
        N'<th><font face="calibri" size="2">Column6</font></th>' +
        CAST ( (    SELECT  td=[Column1],'',
                            --filename is referenced
                            td='<img src="RedTL.gif"/>','',
                            td=[Column2],'',
                            td=[Column3],'',
                            td=[Column4],'',
                            td=[Column5],''
                    FROM [Table1]
                    ORDER BY [Column1]
                  FOR XML PATH('tr'), TYPE 
        ) AS NVARCHAR(MAX) ) +
        N'</table>'

I have other images embedded fine, just having issues with embedding one within the table.

The email will be viewed within Outlook & won't leave the internal network.

Any pointers would be superb!

Thanks

Answer 1

Hopefully this might be of use to someone at some point in time..... To get it working I basically cast the column as XML within the table select, the '' was set against each row within a temp table as they differed depending on date, so it was actually pretty straightforward;

 SET  @TBLHTML=
        N'<STYLE type="text/css">' +
        N'.Table { background-color:#D8E7FB;border-collapse:separate;color:#000;font-size:18px; }' +
        N'.Table th { background-color:#0E0355;color:white; }' +
        N'.Table td, .Table th { padding:5px;border:0; }' +
        N'.Table td { border: 1px dotted white; }' +
        N'</STYLE>' +
        N'<table class="Table">' +
        N'<th><font face="calibri" size="2">Case Attorney</th>' +
        N'<th><font face="calibri" size="2">TL Status</font></th>' +
        N'<th><font face="calibri" size="2">Event Due Date</font></th>' +
        N'<th><font face="calibri" size="2">Event Description</font></th>' +
        N'<th><font face="calibri" size="2">Event No.</font></th>' +
        N'<th><font face="calibri" size="2">Client</font></th>' +
        N'<th><font face="calibri" size="2">Applicant</font></th>' +
        CAST ( (    SELECT  td=[CaseAtt],'',
                            td=CAST([TLImage] AS XML),'',
                            td=CONVERT(VARCHAR(12),[EventDueDate],103),'',
                            td=[EventDesc],'',
                            td=[EventNo.],'',
                            td=[Client],'',
                            td=[Applicant],''
                    FROM #TLREP
                    ORDER BY [CaseAtt]
                  FOR XML PATH('tr'), TYPE 
        ) AS NVARCHAR(MAX) ) +
        N'</table>'

To point out the supposed dupe question post/comment was (very) well wide of what I was actually asking.