output sql data to html with hyperlink

Question

I have below query that converts sql result to HTML

DECLARE @BODY VARCHAR(MAX)


SET @BODY = CAST((
SELECT td = entity + '</td><td>' + procname + '</td><td>' + exedate + '</td><td>' + rowcnt + '</td><td>' + 
cont + '</td><td>' + fpath from (

SELECT entity = ENTITY  , procname = PROC_NAME,exedate = EXEC_DATE, rowcnt = ROW_COUNT,
cont = CONTENT, fpath = FILEPATH FROM HTML_OUTPUTFOREMAIL) AS D
for xml path('tr'),type) as varchar(max))


set @BODY = '<tr><h1>SUMMARY TABLE</h1>'
+'<table cellpadding="2" cellspacing="2" border="1">'
+'<tr><th>ENTITY</th><th>PROC_NAME</th><th>EXEC_DATE</th><th>ROW_COUNT</th><th>CONTENT</th><th>FILEPATH</th><tr>'
+ REPLACE(replace(@body,'&lt;','<'),'&gt;','>')
+'<table>'

print @body

In this data I have a column "fpath" that contains link to the HTML file. But I want the result of this column in a hyperlink.

Answer 1

This is not really SQL Server jobs to output HTML. If you have to create it this way, you can generate the whole table with FOR XML Explicit.

I am just creating a dummy table with 4 line here and dummy data. You still have to change columns name or add more.

declare  @t table(id int, entity varchar(10), name varchar(10), fpath varchar(50), link_name varchar(10), exec_date datetime)
insert into @t(id, entity, name, fpath, link_name, exec_date) values
    (1, 'ent1', 'A', 'google.com', 'linkA', '20150115')
    , (2, 'ent2', 'B', 'google.com', 'linkB', '20150215')
    , (3, 'ent3', 'C', 'google.com', 'linkC', '20150315')
    , (4, 'ent4', 'D', 'google.com', 'linkD', '20150415')

Select Tag, Parent
    , [Table] as 'Table!1!'
    , [entity] as 'TR!2!TD!Element'
    , [name] as 'TR!2!TD!Element'
    , [exec_date] as 'TR!2!TD!Element'
    , [TD] as 'TD!3!TD!Element'
    ,  [href] as 'A!4!href' 
    , [target] as 'A!4!target' 
    ,  [link] as 'A!4!' 
From(
    Select 1 as Tag, NULL as Parent, 0 as sort
        , NULL as 'Table'
        , NULL as 'entity'
        , NULL as 'name'
        , NULL as 'exec_date'
        , NULL as 'TD'
        , NULL as 'href'
        , NULL as 'link'
        , NULL as 'Target'
    Union All
    Select 2 as Tag, 1 as Parent, id*10 as sort
        , NULL
        , entity
        , name
        , cast(exec_date as varchar(50))
        , NULL
        , NULL
        , NULL
        , NULL
    From @t
    Union All 
    Select 3 as Tag, 2 as Parent, id*10+1 as sort
        , NULL
        , NULL
        , NULL
        , NULL
        , NULL
        , NULL
        , NULL
        , NULL
    From @t
    Union All
    Select 4 as Tag, 3 as Parent, id*10+2
        , NULL
        , NULL
        , NULL
        , NULL
        , NULL
        , fpath
        , link_name
        , '_blank'
    From @t
) X
Order By sort
For XML EXplicit