Aliasing rows with hyperlinks when sending HTML table via sp_send_dbmail

Question

I am emailing an HTML table to users but I want to alias the hyperlinks for space reasons. Please have a look on the image.

Here is the Sample1 of the image:

Please see my below code :

    DECLARE @bodyMsg nvarchar(max)
    DECLARE @subject nvarchar(max)
    DECLARE @tableHTML nvarchar(max)
    SET @subject = 'Query Results in HTML with CSS'
    DECLARE @email_to_notify nvarchar(256);
    SELECT @email_to_notify = 'bens@ioec.co.za' ;
    SET @tableHTML = 
    N'<style type="text/css">
...
</tr>' +
CAST ( ( 

SELECT 
td = y.Customer,'',
td = y.ContractNumber,'',
td = y.Description ,'',
td = y.Market ,'',
td = y.BDM ,'',
td = y.EC ,'',
td = y.Reason ,'',
td = y.Link 
FROM (SELECT  FileReference AS 'Reference', Name AS 'Customer' , 
        CASE WHEN ContractNumber IS NULL THEN '' ELSE ContractNumber END AS 'ContractNumber',           
        'http://gateway.ioec.co.za/#/trade/files/' + CONVERT(varchar(10),Gw_File.FileID) AS 'Link'
FROM Gw_File INNER JOIN ... ) y
FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>' 

EXEC msdb.dbo.sp_send_dbmail @recipients='rajivs@ioec.co.za',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;

I would also like to highlight rows depending on value of the reason column.

Answer 1

It seems like that when you're passing your markup, you aren't retaining it's type. Special characters, such as < will be replaced with a string representation to keep the xml valid. For example < & < and & & &.

If it's just the > and < symbols you can use a replace before sending out the email:

SET @tableHTML = REPLACE(REPLACE(@tableHTML,'&gt;','<'),'&lt;','>');

This however, trusts you have no nested HTML.