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,'<','<'),'>','>')
+'<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.
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