htmlsqlsql-serversql-server-2008

output sql data to html with hyperlink


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.


Solution

  • 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