Convert of HTML

SVA 116

Hello

How to convert the html data in a table column to table format and store in another table

Thanks

Erland Sommarskog 112.4K Reputation points MVP

2024-07-10T12:50:41.0333333+00:00

For this type of question, it absolutely helps if you post the CREATE TABLE statement for your table and INSERT statements with data that resembles your real world data together with the expected result.

It is possible that you can use the XML capabilities in SQL Server for this task. However, XML is strict on balancing tags, whereas HTML is more lax, so it may not work out. But we know more when we see your data.
LiHongMSFT-4306 27,426 Reputation points

2024-07-11T02:27:45.6366667+00:00

Hi @SVA

Please post some sample data for better support.
Zahid Butt 956 Reputation points

2024-07-15T11:27:43.52+00:00

Hi @SVA,

Is your issue resolved or not ?

If below solution helped you please mark it as answer.

Thanks

1 answer

Zahid Butt 956 Reputation points

2024-07-10T13:37:53.81+00:00

Hi,

Below is sample code:

DECLARE @html xml =

'

<html>

<header><title>Test</title></header>

<body>

<h1>blah blah</h1>

<table border="1" cellspacing="0" style="border: black 1px solid">

<tr><th>Date</th><th>Sales</th></tr>

<tr><td>1/1/2003</td><td>12.09</td></tr>

<tr><td>1/3/2003</td><td>85.09</td></tr>

</table>

<p>blah blah</p>

</body>

</html>

'

-- code to get extract data from the HTML

SELECT sale_date = xx.value('(./td/text())[1]','varchar(100)'),

sale_amt = xx.value('(./td/text())[2]','varchar(100)')

into #SQL_Table

FROM (values(@html)) t1(x)

CROSS APPLY x.nodes('//table[1]/tr[position()>1]') t2(xx);

select * from #SQL_Table

You may get it from here."https://www.sqlservercentral.com/forums/topic/import-data-from-html-document-into-sql-table"
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Your answer