SELECT .... FOR XML and data alignment in the table

This is taken from SQL 2005 help from sp_send_dbmail article:

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 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,
(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' ;

If you replace email address with yours and run this query on
AdventureWorks database you will receive table in the email (assuming
you configured Database mail). All data in the table cells will be
left-aligned.

Does anyone know how to modify the above so that the data is right-
aligned?

You should be able to use the HTML <td> element "align" attribute to get the
desired result:

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
'right' AS "td/@align",
td = wo.WorkOrderID, '',
'right' AS "td/@align",
td = p.ProductID, '',
'right' AS "td/@align",
td = p.Name, '',
'right' AS "td/@align",
td = wo.OrderQty, '',
'right' AS "td/@align",
td = wo.DueDate, '',
'right' AS "td/@align",
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
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,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr')
) AS NVARCHAR(MAX) ) +
N'</table>' ;

--

========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/


<John_Doe.TakeThisOut@inbox.ru> wrote in message
news:dd9fa015-3b1a-41a4-a9f3-b507351b35ef@s50g2000hsb.googlegroups.com...
> This is taken from SQL 2005 help from sp_send_dbmail article:
>
> 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 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,
> (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' ;
>
> If you replace email address with yours and run this query on
> AdventureWorks database you will receive table in the email (assuming
> you configured Database mail). All data in the table cells will be
> left-aligned.
>
> Does anyone know how to modify the above so that the data is right-
> aligned?