56398

how to count common nodes between two nodes in sql

Question:

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

count <li> tags between <h2 id="vgn">VGN A </h2> and <h2 id="vgn">VGN </h2> and the number of <h2> tags.

In other words: count of li between h2 number1 and h2 number 2 in this example.

<table class="table table-condensed table-no-border table-brand-main"> <tr><td><h2 id="vgn">VGN A </h2></td></tr> <tr><td> <style> ul.brand_572 { -moz-column-count: 6; -webkit-column-count: 6; column-count: 6; } ul.brand_572 li { width:120px; } /*ul.brand_572 li a.product-title { width:120px; }*/ </style> <ul class="specialclass brand_572"> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> </ul> </td> </tr> <tr><td><h2 id="vgn">VGN </h2></td></tr> <tr><td> <ul class="specialclass brand_572"> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> <li> <a href="sony-vgn-ar290g-battery-6cells-4400mah-11-1v.html">Sony VGN-AR290G </a> </li> </ul> </td></tr> </table>

expected output:

count of li between h2 and h2 7 1 2

c.li = 7 between no.h2 1,2

Answer1:

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

<a href="https://stackoverflow.com/q/39434747/5089204" rel="nofollow">As I told you before</a>:

<h2>T-SQL is not the right tool for your problems!</h2>

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

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 - <strong>but you can never rely on this!!!</strong> - your HTML is valid XML. In this case - and <strong>ONLY in this case!</strong> - 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:

<blockquote>

Search for <strong>any h2 anywhere in the document</strong> 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.

</blockquote>

Looks OK, but

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

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

Recommend

  • Android resource size qualifier
  • Contacts - how to distinguish between SIM, phone and other contacts?
  • How to make side ID navigation links active/hover state as I scroll down to their div
  • how to find out carrier's name in javascript or php
  • strategy for syncing Android app database with a remote API
  • Set video as wallpaper on Android
  • Programmatically auto-power on the phone
  • Change colour of vertices after they are uploaded using a VBO
  • d3.js: why is d3.geo.path() giving NaN?
  • How to use animated gif in Firemonkey?
  • MultiLevel ExpandableListView not highlighting properly
  • jQuery - resize an elements height to match window without refreshing, on window resize
  • cell spacing in div table
  • using html data-attributes as css-variable (i.e. text-shadow)
  • Drawing a Path in XAML (with binding) vs. C#. One works, one doesn't
  • Magento get URL before current
  • chrome video src change not working
  • WPF Template Binding in ToggleButton UserControl
  • WPF version of .ScaleControl?
  • blade.php method outputting it's result to the form
  • D3 get axis values on zoom event
  • Excel's Macro-Recorder usage
  • Custom validator control occupying space even though display set to dynamic
  • OpenGL 3.3 on Mac OSX El Capitan with LWJGL
  • Change multiple background-images with jQuery
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Excel - Autoshape get it's name from cell (value)
  • Why doesn't :active or :focus work on text links in webkit? (safari & chrome)
  • vba code to select only visible cells in specific column except heading
  • Change an a tag attribute in JavaScript based on screen width
  • How to show dropdown in excel using jrxml (jasper api)?
  • XCode can't find symbols for a specific iOS library/framework project
  • How to get icons for entities from eclipse?
  • Proper way to use connect-multiparty with express.js?
  • NSLayoutConstraint that would pin a view to the bottom edge of a superview
  • Load html files in TinyMce
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • Getting Messege Twice Using IMvxMessenger
  • python draw pie shapes with colour filled
  • JaxB to read class hierarchy