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

Question

I am creating HTML code that will be included in the body of the message and sent using sp_send_dbmail. I would like to align some columns. Is there an easy way to do this (without rewriting it with 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>'

Answer 1

That should do it. Please note that you do not need to manually add tags <tr>and </tr>to your html line. This data is provided to you as part of for xml path('tr'). You probably would like to add </table>at the end.

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:

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