SQL HTML

• TJT

  SSCommitted

  Points: 1926

  More actions

  April 1, 2015 at 1:20 pm

  #316770

  Hi,

  I am trying to figure out how to change the first <TD> to add BGCOLOR in this script:

  declare @body varchar(max)

  -- Create the body

  set @body = cast( (

  select td = dbtable + '</td><td>' + cast( entities as varchar(30) ) + '</td><td>' + cast( rows as varchar(30) )

  from (

  select dbtable = object_name( object_id ),

  entities = count( distinct name ),

  rows = count( * )

  from sys.columns

  group by object_name( object_id )

  ) as d

  for xml path( 'tr' ), type ) as varchar(max) )

  set @body = '<table cellpadding="2" cellspacing="2" border="1">'

  + '<tr><th>Database Table</th><th>Entity Count</th><th>Total Rows</th></tr>'

  + replace( replace( @body, '<', '<' ), '>', '>' )

  + '<table>'

  print @body

  http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/10/24/format-query-output-into-an-html-table-the-easy-way.aspx

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  April 1, 2015 at 8:18 pm

  #1788174

  Since nobody else is providing a solution I'll give it a try. I am simplifying the query against sys.columns because I don't really understand what you are doing. That said, you can use my example as a template and rearrange the query that feeds the table accordingly.

  WITH x1(xxx) AS

  (

  SELECT TOP(10) td1 = name, td2 = column_id, td3 = max_length

  FROM sys.columns

  FOR xml path('tr'),ROOT('table')

  )

  SELECT

  CAST

  (

  REPLACE

  (

  REPLACE(REPLACE(REPLACE(REPLACE(xxx,'<td1>','<td bgcolor="red">'),'td2','td'),'td3','td'),'</td1>','</td>'),

  '<table>','<table cellpadding="2" cellspacing="2" border="1">'

  ) AS XML

  )

  FROM x1;

  Edit: code formatting issue.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  April 2, 2015 at 5:58 am

  #1788257

  XML attributes can be embedded using [@attrname] = .. syntax with FOR XML

  select

  (select [@bgcolor]='red', object_name( object_id ) for xml path ('td'), type),

  (select count( distinct name ) for xml path ('td'), type),

  td = count( * )

  from sys.columns

  group by object_name( object_id )

  for xml path('tr'), type