September 28, 2010 at 11:58 am
I was looking up how to inline an HTML table in an e-mail when I send mail using Database Mail and I found a solution here: http://msdn.microsoft.com/en-us/library/ms190307.aspx
Here is the code snippet I'm using as an example:
Copy
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks2008R2.Production.WorkOrder as wo
JOIN AdventureWorks2008R2.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2006-04-30'
AND DATEDIFF(dd, '2006-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
@subject = 'Work Order List',
@body = @tableHTML,
@body_format = 'HTML' ;
The example works great, but I have one question... How come I need to include a column followed by an empty string followed by a column...etc... in the subquery? If I don't include the empty string after each column that I'm selecting it doesn't seem to close the TD until the end of the row. Just wondering what's going on here so I can get a better understanding. I'm a bit of an XML n00b.
Thanks!
John
October 11, 2010 at 6:36 pm
SQL assumes that you want all of the columns to be part of one element.
It is very similar to putting "td/@style" = 'color:red', "td/@width" = "55pt", td = ProductID
When you have this expression, it builds the attributes of one td element. Then it adds the text to the td. If you have multiple td aliased items, SQL just adds that information to the td text.
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply