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

Question

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='[e-mail]',
    @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?

Answer 1

You can achieve this by replacing the entire SET @tableHTML statement with this:

 

Code Snippet

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/@align" = 'right', td = wo.WorkOrderID, '',

            "td/@align" = 'right', td = p.ProductID, '',

            "td/@align" = 'right', td = p.Name, '',

            "td/@align" = 'right', td = wo.OrderQty, '',

            "td/@align" = 'right', td = wo.DueDate, '',

            "td/@align" = 'right', 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>' ;

 

 

 

This version simply adds the attribute align="right" to each of the <td> elements in the table body.

 

You can add as many attributes as you want using this method.  You just need to list the attributes before the element to which they apply.  The attribute names need to have the @ prefix to indicate that they are attributes.

 

Hope this helps

 

Iain

Answer 2

Iain,

Thanks a lot - this is exactly what I was looking for! I realised I needed to insert align="right" attribute, I just couldn't figure out how to plug it in the SELECT statement

Answer 3

You can achieve this by replacing the entire SET @tableHTML statement with this:

 

Code Snippet

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/@align" = 'right', td = wo.WorkOrderID, '',

            "td/@align" = 'right', td = p.ProductID, '',

            "td/@align" = 'right', td = p.Name, '',

            "td/@align" = 'right', td = wo.OrderQty, '',

            "td/@align" = 'right', td = wo.DueDate, '',

            "td/@align" = 'right', 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>' ;

 

 

 

This version simply adds the attribute align="right" to each of the <td> elements in the table body.

 

You can add as many attributes as you want using this method.  You just need to list the attributes before the element to which they apply.  The attribute names need to have the @ prefix to indicate that they are attributes.

 

Hope this helps

 

Iain

Thanks it is really useful. 

but is there any way to add  hyperlink <a href=someurl.aspx?qs='p.ProductID,'>p.ProductID,<a>

When i insert this in code it gives this as text but not giving hyperlink.

so i want out put like 

     Product Id

      112