How to add an attribute to element-centric FOR XML PATH query

Question

I'm building some HTML to be included in the body of an email and sent using sp_send_dbmail. I'd like to right-align some of the columns. Is there an easy way to do this (short of rewriting it using FOR XML EXPLICIT)?

declare @html varchar(max)

set @html = '<table cellpadding=0 cellspacing=0 border=0>'

set @html +=  
  cast(
    (select
      'Column1' as td, '',
      'Column2' as td, '',
      'Column3' as [td align=right] /* Would like to do something like this */
    for xml path('tr')) as varchar(max)
  )

set @html += '</table>'

Solution

This should do it. Note that you don't need to manually add the <tr> and </tr> tags to your html string. Those are given to you as part of the for xml path('tr'). You probably meant to add </table> to the end instead.

declare @html varchar(max)

set @html = '<table cellpadding=0 cellspacing=0 border=0>'

set @html +=  
  cast(
    (select
      'Column1' as td, '',
      'Column2' as td, '',
      'right' as [td/@align], 'Column3' as td, '' 
    for xml path('tr')) as varchar(max)
  )

set @html += '</table>'

select @html

Output is:

<table cellpadding=0 cellspacing=0 border=0><tr><td>Column1</td><td>Column2</td><td align="right">Column3</td></tr></table>

Comments

[milivojeviCH]

Extremely nice description of FOR XML capabilities can be found here: sqlserverpedia.com/blog/sql-server-bloggers/xml-paths-of-glo‌​ry