HTML Tags parse using SQL

Question

HTML Tags parse using SQL

Ranga Reddy 1

Below is the sample Input


AAAA


<ul>
<li>#2 </li>
<li>#3 </li>
<li>#6 </li>
</ul>



 BBBBB:


<ul>
<li>Best 295 Business Schools</li>
<li>rrrr</li>
</li>
</ul>

I need output in below format

Viorel 122.6K

If it is a valid XML fragment, then you can try some manipulations like this:

declare @input xml = N'
<p>
    <strong>
    <em>AAAA</em>
    </strong>
</p>
<ul>
    <li>#2 </li>
    <li>#3 </li>
    <li>#6 </li>
</ul>
<p>
    <strong>
    <em>
    <br />BBBBB:</em>
    </strong>
</p>
<ul>
    <li>Best 295 Business Schools</li>
    <li>rrrr</li>
</ul>
'
;
with Q as
(
    select 
        n.value('local-name(.)', 'varchar(max)') as n, 
        trim(n.value('.', 'varchar(max)')) as v,
        row_number() over (order by (select null)) as rn
    from @input.nodes('//*[local-name() = "p" or local-name() = "li"]') t(n)
)
select
    (select top(1) v from Q where n = 'p' and rn < t.rn order by rn desc) as Column1,
    v as Column2
from Q t
where n = 'li'

However, an HTML is not always a valid XML. Maybe you can imply some C#, Python, etc. code.

Yitzhak Khabinsky 26,586 Reputation points

2022-01-14T13:35:39.59+00:00

@Ranga Reddy ,

What's the latest on your end?
Did you have a chance to try the proposed solutions?
LiHong-MSFT 10,056 Reputation points

2022-01-19T06:24:20.207+00:00

Hi @Ranga Reddy
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
If all of the answers are not working or helpful, please provide more sample data or details about your issue.
Thank you for understanding!

Best regards,
LiHong

3 answers

Your answer

Viorel 122.6K Reputation points

2022-01-13T15:10:29.217+00:00

If it is a valid XML fragment, then you can try some manipulations like this:

declare @input xml = N' AAAA <ul> <li>#2 </li> <li>#3 </li> <li>#6 </li> </ul> BBBBB: <ul> <li>Best 295 Business Schools</li> <li>rrrr</li> </ul> ' ; with Q as ( select n.value('local-name(.)', 'varchar(max)') as n, trim(n.value('.', 'varchar(max)')) as v, row_number() over (order by (select null)) as rn from @input.nodes('//*[local-name() = "p" or local-name() = "li"]') t(n) ) select (select top(1) v from Q where n = 'p' and rn < t.rn order by rn desc) as Column1, v as Column2 from Q t where n = 'li'

However, an HTML is not always a valid XML. Maybe you can imply some C#, Python, etc. code.
Yitzhak Khabinsky 26,586 Reputation points

2022-01-14T13:35:39.59+00:00

@Ranga Reddy ,

What's the latest on your end?
Did you have a chance to try the proposed solutions?
LiHong-MSFT 10,056 Reputation points

2022-01-19T06:24:20.207+00:00

Hi @Ranga Reddy
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
If all of the answers are not working or helpful, please provide more sample data or details about your issue.
Thank you for understanding!

Best regards,
LiHong

Answer 1

Olaf Helper 47,436

I need output in below format

In SQL? I would say close to impossible. HTML is a markup language for text presentation & formating, it's not structured data.

Answer 2

Hi @Ranga Reddy ,

HTML could be a legitimate (X)HTML.
In such case, it is very easy to process in T-SQL via its XQuery support.

Check it out.

T-SQL

DECLARE @input XML =   
N'<p>  
     <strong>  
     <em>AAAA</em>  
     </strong>  
 </p>  
 <ul>  
     <li>#2 </li>  
     <li>#3 </li>  
     <li>#6 </li>  
 </ul>  
 <p>  
     <strong>  
     <em>  
     <br />BBBBB:</em>  
     </strong>  
 </p>  
 <ul>  
     <li>Best 295 Business Schools</li>  
     <li>rrrr</li>  
 </ul>';  
  
SELECT seq  
	, p.value('(strong/em/text())[1]', 'VARCHAR(100)') AS col1  
	, li.value('text()[1]', 'VARCHAR(100)') AS col2  
FROM @input.nodes('/p') AS t1(p)  
	CROSS APPLY (  
		SELECT t1.p.value('let $i := . return count(/p[. << $i]) + 1', 'INT')  
	) AS t2(seq)  
	OUTER APPLY @input.nodes('/ul[sql:column("t2.seq")]/li') AS t3(li);

Output

+-----+--------+---------------------------+  
| seq |  col1  |           col2            |  
+-----+--------+---------------------------+  
|   1 | AAAA   | #2                        |  
|   1 | AAAA   | #3                        |  
|   1 | AAAA   | #6                        |  
|   2 | BBBBB: | Best 295 Business Schools |  
|   2 | BBBBB: | rrrr                      |  
+-----+--------+---------------------------+

LiHong-MSFT 10,056 Reputation points

2022-01-14T05:44:22.013+00:00

Hi,@Ranga Reddy
Note：Refer to this document for more details about how to use the xml data type methods.

Answer 3

HTML could be a legitimate (X)HTML.
In such case, it is very easy to process in T-SQL via its XQuery support.

Indeed. Hopefully that is the case for Ranga.

However, HTML is more lax than XML. XML requires all tags to be closed. In the above sample, you have  , which is good. But HTML permits   only. And if your HTML looks like that, you cannot convert it to the XML data type. So how you do parse it in that case? Not in T-SQL, that much is clear. If you want to do it from SQL Server, you would have to use the CLR, or call a script in Python or similar if you have the Machine Learning Extensions installed. (For Python, you need SQL 2017 or later.)

Share via

HTML Tags parse using SQL

3 answers

Your answer