I’ve got a DBmail script with an embedded XML table that works perfectly. However, I have one column which uses a dynamic field to create a URL. Within the XML table, I want this to be a hyperlink when sending the email. Currently, it displays the entire string in the email.

Here is my XML script – some identifiable bits cut out

SET @xml = CAST((
            SELECT 
                PolicyReference AS 'td','',
                BusinessUnit AS 'td','',
                PolicyDescription AS 'td','',
                CONVERT(NVARCHAR, ExpiryDate, 103) AS 'td','',
                ExpiryDays AS 'td','',
                ClientName AS 'td','',
                -- Create the hyperlink using HTML tags
               CONCAT('<a href="', PolicyLink, '">Go To Policy</a>') AS 'td','',
                FORMAT(CAST([Policy_Outstanding_GBP] AS DECIMAL(18,2)), '#,0.00') AS 'td'
            FROM 
                (SELECT DISTINCT PolicyReference, BusinessUnit, PolicyDescription, ExpiryDate, ExpiryDays, ClientName, [Policy_Outstanding_GBP], 
                    'https://app.powerbi.com/groups/&experience=power-bi&filter=PolicyAnalysisQuery%2FPolicyReference%20eq%20%27' 
                    + PolicyReference + '%27' AS PolicyLink
                 FROM TABLE_NAME a
                 WHERE ProductClass = @BusinessUnit) AS t
            ORDER BY ExpiryDate
            FOR XML PATH('tr'), ELEMENTS
        ) AS NVARCHAR(MAX));

And here is the email body

-- Construct table for the current BusinessUnit
SET @UnitTable = '<p>' + @BusinessUnit + '</p>
        <table border="1" style="font-size: 10pt;">
        <tr><th style="background-color: #1E90FF; color: white;">Policy Ref</th><th style="background-color: #1E90FF; color: white;">Unit</th><th style="background-color: #1E90FF; color: white;">Description</th>
        <th style="background-color: #1E90FF; color: white;">Expiry Date</th><th style="background-color: #1E90FF; color: white;">Days</th><th style="background-color: #1E90FF; color: white;">Client(s)</th><th style="background-color: #1E90FF; color: white;">Policy Link</th><th style="background-color: #1E90FF; color: white;">Outstanding Premium</th></tr>' +
        @xml +
        '</table>';

In the email that gets sent, it displays the full URL in the column, the link works but it should be a hyperlink on Go To Policy

<a href="https://app.powerbi.com/groups/d3db31d9-199f-4b17-b34f-77db995e3f7c/reports/63088f&experience=power-bi&filter=PolicyAnalysisQuery%2FPolicyReference%20eq%20%27MYPOLICYREFERENCE%27">Go To Policy</a>

Can anyone help me at all get it to display as a hyperlink instead of the full URL?

Tried multiple variations of the code already but can’t seem to get anything to work