OK, here is a solution.
First you need to alter your html input so that it has a format we can work with to parse into a table. The code below uses substring and replace to convert the html into a CSV. Obviously it relies on the html sticking to a format:
Set @html = substring(@html,charindex('<td align=''center''>',@html,0),4000)
Set @html = replace(@html, '<tr>','')
Set @html = replace(@html,'</tr>','#')
Set @html = replace(@html,'<td align=''center''>','''')
Set @html = replace(@html,'</td>','"',')
Set @html = replace(@html,',
#','#')
Now we have @html as a CSV file using ',' as a field delimiter " as a text delimiter and # as a row delimiter.
I then have this natty function which can convert a CSV in a variable into a table. Note that this function relies on DelimitedSplit8K. Note that the function will dynamically create column names if none are supplied. You could pre-pend the final @html variable with a header row however. :-
CREATE PROCEDURE [dbo].[GetCSVasTable]
(
@fileentry varchar(200),
@headerrow int,
@datarow int,
@rowdelimiter varchar(10),
@fielddelimiter varchar(10),
@textdelimiter varchar(10)
)
AS
IF OBJECT_ID('tempdb..#myrows') IS NOT NULL DROP TABLE #myrows
IF OBJECT_ID('tempdb..#myrowsfixed') IS NOT NULL DROP TABLE #myrowsfixed
create table #myrows
(rowid int,rowstring varchar(max))
create table #myrowsfixed
(rowid int, rowstring varchar(max))
declare @mycsv as varchar(max)
set @mycsv=@fileentry
insert into #myrows
select * from [dbo].[DelimitedSplit8K](@mycsv,@rowdelimiter)
OPTION (MAXRECURSION 32000)
IF (select count(*) from #myrows) =1
BEGIN
Select 'Row delimiter could not be found in file'
RETURN
END
;WITH x AS
(
SELECT x.rowstring as n,x.rowid as nid, s.[itemnumber] as [Index], s = REPLACE(s.item, @fielddelimiter,
CASE s.[itemnumber]%2 WHEN 0 THEN '' ELSE @fielddelimiter END)
FROM #myrows AS x
CROSS APPLY dbo.DelimitedSplit8K(x.rowstring, @textdelimiter) AS s
)
insert into #myrowsfixed
SELECT x.nid,fixed = (SELECT x2.s
FROM x AS x2
WHERE x2.nid = x.nid
ORDER BY [Index]
FOR XML PATH, TYPE).value(N'.[1]',N'varchar(max)')
FROM x
GROUP BY x.nid;
declare @myheader as table
(rowid int, colname varchar(500))
insert into @myheader
select * from dbo.DelimitedSplit8K((select rowstring from #myrowsfixed where rowid=@headerrow),@fielddelimiter)
where item is not null
select * from dbo.DelimitedSplit8K((select rowstring from #myrowsfixed where rowid=2),',')
IF (select count(*) from @myheader)=0
BEGIN
insert into @myheader
select * from dbo.DelimitedSplit8K((select rowstring from #myrowsfixed where rowid=2),',')
where item is not null
END
declare @minrow int,@maxrow int,@script varchar(max), @mycol varchar(500)
set @minrow=1
set @maxrow = (select max(rowid) from @myheader)
set @script='Select '
WHILE @minrow<=@maxrow
BEGIN
IF @headerrow=1 set @mycol=(select colname from @myheader where rowid=@minrow) else set @mycol='COL' + cast(@minrow as varchar)
set @script=@script + @mycol + ' = MAX(CASE WHEN ca.ItemNumber = ' +cast(@minrow as varchar)+' THEN Item ELSE '''' END),'
set @minrow=@minrow+1
END
print @script
set @script=left(@script,len(@script)-1) + 'FROM #myrowsfixed csv
CROSS APPLY dbo.DelimitedSplit8K(csv.rowstring,''' + @fielddelimiter+''') Ca
WHERE rowid>='+cast(@datarow as varchar)+'
GROUP BY csv.rowid
;'
print(@script)
exec(@script)
