Click here to Skip to main content
Click here to Skip to main content
Go to top

HTML Table to SQL table conversion

, 2 Jan 2013
Rate this:
Please Sign up or sign in to vote.
HTML table to SQL table conversion.

Introduction

HTML Table to SQL table conversion - An example.

Using the code  

First parse value by <TR> tag and then by <TD>.

Here it is an example or a trick... 

Create this function that will used for parse values  

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ParseValues]
(@String Nvarchar(8000), @Delimiter Nvarchar(max) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val Nvarchar(max))
AS
BEGIN
    DECLARE @Value Nvarchar(max)
    WHILE @String is not null
    BEGIN
        SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(
          @String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, 
          @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(
          @String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
        INSERT INTO @RESULTS (Val)
        SELECT @Value
    END
RETURN
END

Now pass HTML table and execute query 

DECLARE @data Nvarchar(Max)
SET @Data= N'<table>    
            <tr><td>hello</td><td>1</td></tr>
            <tr><td>welcome</td> <td>2</td></tr> 
            </table>'

    select convert(Nvarchar(max),[1]) as col1,[2] col2 from
    (
        select replace(b.val,'</td>','') data,rowno,id-1 as colno from
        (
            select replace( replace(a.val,'</tr>',''),'</table>','') as row,
                    row_number() over (order by a.id) as rowno 
            from dbo.parsevalues(@Data,'<tr>')  a
            where a.val like '<td>%' 
        ) as a
        cross apply  dbo.parsevalues(row,'<td>') as b 
        where  b.id <> 1
    )  as a

PIVOT
(
 max(data)
  FOR colno
  IN (
        [1],[2]
     )
) PivotTable

Happy coding!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Aarti Meswania
Software Developer
India India
It's amazing maths of technology...
Expand knowledge by sharing it.

Comments and Discussions

 
QuestionHow to use this if the HTML has foreign language characters PinmemberMember 1107528211-Sep-14 3:47 
AnswerRe: How to use this if the HTML has foreign language characters PinmemberAarti Meswania11-Sep-14 8:03 
AnswerRe: How to use this if the HTML has foreign language characters PinmemberAarti Meswania11-Sep-14 8:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 2 Jan 2013
Article Copyright 2013 by Aarti Meswania
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid