DB Mail - "For Each Row" instead of FOR XML PATH

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DB Mail - "For Each Row" instead of FOR XML PATH

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-12-28 : 17:44:05

I have a couple of problems:

1)
I'm using the "FOR XML PATH" to generate an HTML table of data in an email. However, the information found online is scarce, so I'm seeking some expert help. I have fields that I need to SELECT because the data is JOINed to another table, but the examples I found online for "FOR XML PATH" change the XML IDs into <td> tags. This is great if I actually want to display every single column of data, but I do not. If you look in my example below, I've colored red the fields that I don't actually want. Though it doesn't generate a <td> tag in the HTML email, it still does leave the XML tags (see screenshot):

Is there a way to change this functionality to simply not bring this unintended XML data over? I was hoping for an option that looked similar to the following:

quote:


FOR EACH ROW in RECORDSET
   <td>wo.WorkOrderID</td>p.name<td>CONVERT(VARCHAR(12), wo.DueDate, 107)</td><td><a href="http://url.com/?querystring">Details</a></td>
LOOP

2)
Note in the screenshot that the hyperlink in the last column does not work, as the "less than" symbol (<) is converted to the HTML code "<". The reasoning behind my request for a "FOR EACH ROW"-like functionality is to have more control over the HTML code sent to the clients, so hopefully if that is possible then it is possible to fix this bug as well.

Thanks,
Matt

Here is the entire script

quote:


DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
	N'<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">' +
	N'<html xmlns="http://www.w3.org/1999/xhtml">' +
	N'<head>' +
	N'<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />' +
	N'<title>Support Contracts Expiring</title>' +
	N'<style type="text/css">' +
	N'<!--' +
	N'body {font: 100% Calibri, Verdana, Arial, Helvetica, sans-serif;background: #ffffff;margin: 0;padding: 0;text-align: center; color: #000000;}' +
	N'.oneColElsCtr #container {width: 90%;background: #FFFFFF;margin: 0 auto;border: 1px solid #000000;text-align: left;}' +
	N'.oneColElsCtr #title {font-family: Calibri, Tahoma, Verdana, Arial, Helvetica, sans-serif;font-size:36px;font-weight:bold;background:#373737;text-align:left;padding:10px;}' +
	N'.oneColElsCtr #footer {font-family: Calibri, Tahoma, Verdana, Arial, Helvetica, sans-serif;font-size:11px;background:#080240;color:#ffffff;text-align:left;padding:10px;}' +
	N'.formsuperheader {font-family:Calibri, Tahoma, Verdana, Arial, Helvetica, sans-serif;font-weight:bold;font-size:20px;font-weight:bold;color:#4f81bd;padding:18px;}' +
	N'.formheader {font-family:Calibri, Tahoma, Verdana, Arial, Helvetica, sans-serif;font-size:14px;font-weight:bold;color:#092D71;padding-left:22px;margin-bottom:8px;}' +
	N'.formquestion {font-family:Calibri, Tahoma, Verdana, Arial, Helvetica, sans-serif;font-size:14px;color:#333333;padding-left:28px;padding-top:5px;}' +
	N'-->' +
	N'</style></head>' +
	N'<body class="oneColElsCtr">' +
	N'<table id="container" cellpadding="0" cellspacing="0">' +
	N'<tr><td id="title"><span style="color:#eaeaea;">Support Contracts</span></td></tr>' +
	N'<tr><td class="formsuperheader">Support Contracts Expiring</td></tr>' +
	N'<tr><td class="formheader">The following support contracts are expiring soon. Please review them and contact the customers for renewal.</td></tr>' +
	N'<tr><td class="formquestion">' +
    N'<table border="0" cellpadding="0" cellspacing="5">' +
    N'<tr><th>Contract ID</th>' +
    N'<th>Customer</th><th>Expiration Date</th><th>Actions</th>' +
    CAST ( ( SELECT td = wo.WorkOrderID, '',
                    p.ProductID, '',
                    td = p.Name, '',
                    wo.OrderQty, '',
                    td = CONVERT(VARCHAR(12), wo.DueDate, 107), '',
		    td = '<a href="http://someurl.com/contract.asp?ID=363543">View Details</a>'
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2 
              ORDER BY DueDate ASC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' +
	N'</td></tr><tr><td> </td></tr>' +
	N'<tr><td id="footer">This email was blah blah blah... If you believe that you received this email in error, you may safely ignore it.</td></tr>' +
	N'</body>' +
	N'</html>';


EXEC msdb.dbo.sp_send_dbmail 
	@profile_name = 'Support Contracts',
    @recipients='someguy@somedomain.com',
    @subject = 'Support Contracts Expiring',
    @body = @tableHTML,
    @body_format = 'HTML' ;

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-12-28 : 18:01:11

For reference, here is the MSDN article that the code is based off of using the Adventureworks DB: http://msdn.microsoft.com/en-us/library/ms190307.aspx

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-12-29 : 17:24:12

UPDATE: for anyone who came here via a search engine, I gave up.

Instead of using database mail through a SQL Server Agent job, I'll just use cURL as a scheduled task on the database server. Then, cURL can hit an ASP page that will output the data from the DB and email it. A less sexy process, but will apparently allow me to better-format the email.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources