parsing @table values out into a HTML table

Question

parsing @table values out into a HTML table

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

eharper SSC Eights! Points: 802 More actions · Answer 1

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 · Answer 2

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