How to force table cell alignment in a SQL Query for XML...

[Peager]

Given the following example code from the MS docs, how would one force alignment on the SELECT td = .... elements? Is there some secret documentation mother load in the sky I haven't found yet documenting how this works?

Much thanks in advance,

Paul

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

 

[markros]

Take a look at a very similar code (I believe it includes alignment)

http://beyondrelational.com/justlearned/posts/58/output-table-as-html-table.aspx

PluralSight Learning Library

 

[Peager]

Wow! And I thought I was pretty good at understanding TSQL code. Yes, this exampled does handle alignment. Now if I can just boil it down to something I can get my head around.

Many thanks,

Paul

 

[jbenson001]

Why even do this in SQL? You should use a front end program. If you are familiar with .NET, this is a breeze and requires no SQL other than the getting the base data.

 

[Peager]

We are using SQL Agent to send an e-mail via sp_send_dbmail. sp_send_dbmail has the ability to generate an HTML body to the message.

 

[jbenson001]

I see that. However, I still believe that SQL is not the place for this. If you need an automated way of sending emails, and you need an HTML format, than the best idea would to use a .NET service that runs in the background. You can set it to run at any interval, and as I said before, it would be much easier, and cleaner, to generate the HTML with .NET.

Just something to think about, especially if you want to generate any complicated HTML with CSS, etc.