How To Count Common Nodes Between Two Nodes In Sql

How to count common nodes between two nodes in sql for this example: count

tags between

VGN A

and

VGN

and

Solution 1:

You've placed three questions now, all about some issues with HTML you want to solve with T-SQL.

As I told you before:

T-SQL is not the right tool for your problems!

I've told you there that you have a deep misconception of XML. And you've done it again: You tagged your question with xml. BUT THIS IS NOT XML (even if it looks like)! THIS IS HTML!

SQL Server gives great support with XML but is not the right tool for HTML. Use HTML parser or any procedural language to write your parsing algorithm yourself.

Just do give you an idea, why SQL Server is not the right tool:

In your special case - but you can never rely on this!!! - your HTML is valid XML. In this case - and ONLY in this case! - one could think about XQuery. Counting the <li>-elements after the <h2> element might look like this:

DECLARE @s VARCHAR(MAX)='Your HTML here';
DECLARE @xml XML=CAST(@s AS XML); --you'd get your first error here probably

DECLARE @H2_id VARCHAR(100)='vgn';
SELECT COUNT(*) AS CountOnThisLevel
FROM @xml.nodes('//h2[@id=sql:variable("@H2_id")]/../../../tr[2]/td/ul/li') AS A(li)

The result is 7, as you like it...

This path

//h2[@id=sql:variable("@H2_id")]/../../../tr[2]/td/ul/li

reads like this:

Search for any h2 anywhere in the document with an id-attribute equal to the content of the variable "H2_id". Now move three levels up, then take the second <tr>, there move down the tree to <td>, <ul> and <li>. Count how many you find there.

Looks OK, but

• <h2> might be <H2> (XQuery is case-sensitive!)
• a HTML table might use <thead> and <tfoot>
• there could be <div>-tags in the chain
• and - almost sure! - your real-world HTML is not valid XML
• and many more reasons, why this approach is bad!

You should really turn around and look for the proper tool!