parsing @table values out into a HTML table

• tksmith

  SSC Enthusiast

  Points: 145

  More actions

  September 25, 2008 at 2:19 am

  #198569

  I'm sending a notification email in HTML format and there is data in a table function type i want to send in the email as a html table. is there a neat way to do it or do i need to get messy looping though the table value?

  the IDEAL solution would have been if reporting services subscription model wasn't so primative and could send reports in a conditional manner. if i could suck in a report and send it via email from an sql server job (like below) that'd be great as well - unfortunately that's way way beyond my skill level.

  DECLARE @results as table(target_name VARCHAR(50), target_value numeric);

  INSERT INTO @results (target_name , target_value )

  (SELECT target_name , target_value from foo);

  IF

  (SELECT count(*) from @results) > 0

  BEGIN

  EXEC msdb.dbo.sp_send_dbmail @recipients='me@foobar.com',

  @subject = 'Approching targets',

  @body = @results, <-- convert that guy into a html table

  @body_format = 'HTML'

  END;

• eharper

  SSC Eights!

  Points: 802

  More actions

  September 25, 2008 at 4:56 am

  #876881

  This code dispenses with the need to create a table variable; results are drawn directly from your table "foo" using the FOR XML clause to do the tagging.

  Whilst this works OK, I suspect there's a smarter way of constructing the HTML table; I couldn't work out how else to generate the table header and detail elements in the same query.

  DECLARE @xml1 as nvarchar(max)

  DECLARE @mailbody as nvarchar(max)

  -- Set up table and th tags

  SET @xml1 =

  (SELECT Tag

  ,Parent

  ,[table!1!border]

  ,[tr!2!th!ELEMENT]

  ,[tr!2!tn!ELEMENT] AS [tr!2!th!ELEMENT]

  FROM

  (SELECT 1 AS Tag

  ,NULL AS PARENT

  ,1 AS [table!1!border]

  ,NULL AS [tr!2!th!ELEMENT]

  ,NULL AS [tr!2!tn!ELEMENT]

  UNION ALL

  SELECT 2 AS Tag

  ,1 AS Parent

  ,NULL AS [table!1!border]

  ,'name' AS [tr!2!td!ELEMENT]

  ,'value' AS [tr!2!tn!ELEMENT]

  ) AS a

  for XML EXPLICIT)

  -- Set up td tags

  SET @mailbody =

  (SELECT 1 AS Tag

  ,NULL AS Parent

  ,target_name AS [tr!1!td!ELEMENT]

  ,target_value AS [tr!1!td!ELEMENT]

  FROM foo

  FOR XML EXPLICIT)

  -- Concatenate header and detail together

  -- SET @mailbody = replace(@xml1,' ')

  The SQLServerCentral comment filter prevents the line above from being displayed correctly; I've attached a text file with the complete code

  -- @mailbody can now be used in mail, or whatever

  SELECT @mailbody

• tksmith

  SSC Enthusiast

  Points: 145

  More actions

  September 25, 2008 at 4:03 pm

  #877381

  hmm yeah i really wanted to stay away from storing any formatting in the sql code, doesn't gel with our projects layout of data->business rules->formatting

  maybe i've just uncovered a new feature request