Basic xml formatting question

• Harold Buckner

  SSCarpal Tunnel

  Points: 4175

  More actions

  September 2, 2010 at 8:12 am

  #100303

  I am creating a table as a message to be emailed using the sp_send_dbmail. The question I have is there any way to apply the align=" center" to the td cells?

  For instance, the td = [AllowableDaysBetweenBackups] I would like to center the results. Is there any way to accomplish this in code I have below.

  Thanks

  DECLARE @tableHTML NVARCHAR(MAX) ;

  SET @tableHTML = N'<table border="10" bgcolor="#CCFFFF" BORDERCOLOR="#0000FF"

  BORDERCOLORLIGHT="#33CCFF" BORDERCOLORDARK="#0000CC">' + N'<tr><th>Server</th>'

  + N'<th>Database</th>' + N'<th>Status</th>'

  + N'<th> Days Allowed Between Backups</th>' + N'<th>DB Status</th>'

  + N'<th>Latest Backup</th></tr> '

  + CAST(( SELECT td = [server] ,

  '' ,

  td = [DatabaseName] ,

  '' ,

  td = [Status] ,

  '' ,

  td = [AllowableDaysBetweenBackups] ,

  '' ,

  td = [DatabaseStatus] ,

  '' ,

  td = ISNULL(CONVERT(VARCHAR(20),[LastBackup],100),'N\A') ,

  ''

  FROM dbo.vw_DatabasesMissingBackups

  ORDER BY 1 ,

  2 ,

  3 ,

  4 ,

  5

  FOR

  XML PATH('tr') ,

  TYPE

  ) AS NVARCHAR(MAX)) + N'</table>' ;

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  September 3, 2010 at 12:15 pm

  #1216363

  You can specify attributes using [path/@attr]. For example,

  SELECT

  'center' AS ,

  td = [server] ,

  '' ,

  'center' AS ,

  td = [DatabaseName] ,

  '' ,

  'center' AS ,

  td = [Status] ,

  '' ,

  'center' AS ,

  td = [AllowableDaysBetweenBackups] ,

  '' ,

  'center' AS ,

  td = [DatabaseStatus] ,

  '' ,

  'center' AS ,

  td = ISNULL(CONVERT(VARCHAR(20),[LastBackup],100),'N\A') ,

  ''

  FROM dbo.vw_DatabasesMissingBackups

  ORDER BY 1 ,

  2 ,

  3 ,

  4 ,

  5

  FOR

  XML PATH('tr')

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Harold Buckner

  SSCarpal Tunnel

  Points: 4175

  More actions

  September 3, 2010 at 12:35 pm

  #1216375

  Awesome! Thanks:-D