HTML formatting only being partially applied using FOR XML PATH and sp_send_dbmail

Question

I have a SP that identifies and removes old db information that is no longer needed, compiling a list of the accounts that were removed.

I'm sending out an email with the list of these accounts, but for some reason the HTML formatting is only being applied to a part of the message body, can anyone offer a reason/solution to this issue?

DECLARE @table TABLE (acct varchar(4))
INSERT INTO @table (acct)
SELECT 1234
UNION ALL SELECT 2345
UNION ALL SELECT 3456
UNION ALL SELECT 4567

DECLARE @accountList VARCHAR(MAX)
SET @accountList = 
    STUFF((SELECT ' <br /> ' + acct FROM @table FOR XML PATH('')),1,1,'')

DECLARE @mailBody VARCHAR(MAX)
SELECT  @mailBody = 'The job ran successfully.' +
    '<br /><br />Account List Purged: <br />' + @accountList

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Default',
    @recipients = 'email@email.email',
    @subject = 'Data Purge [Accounts Purged]',
    @body_format = 'html',
    @body = @mailBody

Here's what gets sent from the exec msdb.dbo.sp_send_dbmail

Notice how the <br /> successfully "applied" for areas outside of the FOR XML PATH..., but within it, are written as literals.

Anyone have any experience with this and/or know what i need to do to remediate?

Answer 1

You should use FOR XML PATH(''), type).value('.[1]','nvarchar(max)').

SET @accountList = 
    STUFF((
        SELECT ' <br /> ' + acct 
        FROM @table FOR XML PATH(''), type).value('.[1]','nvarchar(max)'),
    1, 1, '')

This prints:

<br /> 1234 <br /> 2345 <br /> 3456 <br /> 4567

While your query prints:

<br /> 1234 <br /> 2345 <br /> 3456 <br /> 4567

FOR XML PATH('') has problems with XML entitization as it converts <, > and some other special characters to XML-safe entities. Using PATH, TYPE).value() prevents this from happening.