October 20, 2011 at 4:07 pm
Hi all,
I'm helping a developer establish a framework and have run into a problem I can't resolve efficiently. We're using DB Mail to generate automated emails, the following is how DB Mail is called:
EXEC msdb..sp_send_dbmail
@profile_name = 'DB Mail',
@recipients = 'abc@xyz.com',
@subject = 'HTML Test',
@body_format = 'HTML',
@body = @TableHTML
Here's where we are setting the value of @TableHTML:
SET @TableHTML =
N'<html><body>Please note the following outstanding items from the audit requirements listing, which you have requested. Please ensure that all items are updated to current status by 3 pm today.' +
N'</br>' +
N'<b><u>Overdue Items</u></b>' +
N'<table border="1">' +
N'<tr align="center"><b><td>Item #</td><td>Description</td><td>Suggested Due Date</td><td>Contact</td></b></tr>' +
CAST ((SELECT td = ISNULL(ItemID,''), '',
td = ISNULL(DocumentName,''), '',
td = ISNULL(DueDate,''), '',
td = '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''
FROM dbo.DT_DocumentTracker WHERE RequestStatus IN('Open','Re-Open') AND DTTContact = @DTTContact FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +
N'</table>' +
N'</body></html>'
The problem we have is with this line:
td = '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''
SQL will not recognise that as a HTML link and pastes the whole thing into a table cell, like this:
<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>
The link works (when mailto:abc@xyz.com is clicked a new email is opened with the To Address, CC and Subject fields filled out). However all that should appear in the table's cell is mailto:abc@xyz.com (as the link), not the underlying HTML code.
This works when it is not part of the table (when I put this in a separate line) which is fair enough. I've tried enclosing this line in it's own HTML tags but to no avail. Any thoughts?
Thanks in advance.
October 20, 2011 at 7:03 pm
i believe the issue is this;
FOR XML PATH('tr'), TYPE)
in that case, TYPE escapes out all the brackets (less than,greaterthan, quotes, etc) that would have been HTML.
FOR XML PATH('tr'))
will leave the brackets in place, instead of escaping them into amp lt ;
Lowell
October 20, 2011 at 7:31 pm
Lowell (10/20/2011)
i believe the issue is this;FOR XML PATH('tr'), TYPE)
in that case, TYPE escapes out all the brackets (less than,greaterthan, quotes, etc) that would have been HTML.
FOR XML PATH('tr'))
will leave the brackets in place, instead of escaping them into amp lt ;
Thanks, but that doesn't seem to be the issue. The table cell still contains the whole href part as well, no changes to when it's run with the TYPE included.
October 23, 2011 at 1:18 am
Sorry to bump this, but I really need an answer. Has anyone got any ideas please?
October 24, 2011 at 12:42 pm
mpartridge (10/20/2011)
The problem we have is with this line:td = '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''
SQL will not recognise that as a HTML link and pastes the whole thing into a table cell, like this:
<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>
If you want an XML fragment to retain the special characters the leaf of the node needs to be either [*] or [node()]. Since your leaf (td) is neither of these, it escapes all of the special codes such as ("<", ">", etc.). Try the following code instead.
= '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2011 at 4:02 pm
If you want an XML fragment to retain the special characters the leaf of the node needs to be either [*] or [node()]. Since your leaf (td) is neither of these, it escapes all of the special codes such as ("<", ">", etc.). Try the following code instead.
= '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''
Drew
I tried that but it still does not work, here's what I changed the line to:
= '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''
Here's what the @TableHTML variable now looks like:
SET @TableHTML =
N'<html><body>
Please note the following outstanding items from the audit requirements listing, which you have requested. Please ensure that all items are updated to current status by 3 pm today. '+
N'</br>' +
N'<b><u>Overdue Items</u></b>' +
N'<table border="1">' +
N'<tr align="center"><b><td>Item #</td><td>Description</td><td>Suggested Due Date</td><td>RBS Contact</td></b></tr>' +
CAST ((SELECT
= ISNULL(ItemID,''), '',
= ISNULL(DocumentName,''), '',
= ISNULL(DueDate,''), '',
= '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''
FROM dbo.DT_DocumentTracker WHERE RequestStatus IN('Open','Re-Open') AND DTTContact = @DTTContact FOR XML PATH('tr')) AS NVARCHAR(MAX)) +
N'</table>' +
N'</body>'
Hmm, it's not meant to be hard but can't for the life of me figure out what I'm missing...
October 25, 2011 at 4:42 am
FOR XML EXPLICIT seems to do the trick.
SET @TableHTML =
N'<html><body>Please note the following outstanding items from the audit requirements listing, which you have requested. Please ensure that all items are updated to current status by 3 pm today.' +
N'</br>' +
N'<b><u>Overdue Items</u></b>' +
N'<table border="1">' +
N'<tr align="center"><b><td>Item #</td><td>Description</td><td>Suggested Due Date</td><td>Contact</td></b></tr>' +
CAST ((SELECT td = ISNULL(ItemID,''), '',
td = ISNULL(DocumentName,''), '',
td = ISNULL(DueDate,''), '',
td =
(SELECT
1 AS Tag, NULL AS Parent,
[a!1] = 'mailto:abc@xyz.com',
[a!1!href] = 'mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com'
FOR XML EXPLICIT, TYPE
), ''
FROM dbo.DT_DocumentTracker WHERE RequestStatus IN('Open','Re-Open') AND DTTContact = @DTTContact FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +
N'</table>' +
N'</body></html>'
October 25, 2011 at 10:31 am
Or even simpler
SET @TableHTML =
N'<html><body>Please note the following outstanding items from the audit requirements listing, which you have requested. Please ensure that all items are updated to current status by 3 pm today.' +
N'</br>' +
N'<b><u>Overdue Items</u></b>' +
N'<table border="1">' +
N'<tr align="center"><b><td>Item #</td><td>Description</td><td>Suggested Due Date</td><td>Contact</td></b></tr>' +
CAST ((SELECT td = ISNULL(ItemID,''), '',
td = ISNULL(DocumentName,''), '',
td = ISNULL(DueDate,''), '',
td = CAST('<a href="mailto:abc@xyz.com?Subject=Mailto%20Test& amp;cc=xyz@abc.com">mailto:abc@xyz.com</a>' AS XML), ''
FROM dbo.DT_DocumentTracker WHERE RequestStatus IN('Open','Re-Open') AND DTTContact = @DTTContact FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +
N'</table>' +
N'</body></html>'
Note the replacement of the '&'-character with '& amp;' in the href-attribute.
EDIT: the space in '& amp;' is only added to show the sequence in this html-page. Otherwise it is translated back into '&'.
October 25, 2011 at 6:24 pm
That worked! Thanks very much Peter, I hadn't considered casting as XML.
Cheers
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply