正如我以前告诉过你的那样：
T-SQL不适合您的问题！
我已经告诉过你对XML有深刻的误解。而且你已经做了一遍：你用 xml 标记了你的问题。 但这不是XML（即使它看起来像）！这是HTML！
SQL Server为XML提供了很好的支持，但它不适用于HTML。使用HTML解析器或任何过程语言自己编写解析算法。
只要给你一个想法，为什么SQL Server不是正确的工具：
在您的特殊情况下 - 但您永远无法依赖此!!! - 您的HTML是有效的XML。在这种情况下 - 和仅在这种情况下！ - 可以考虑 XQuery 。在< h2> 元素之后计算< li> -elements可能如下所示：
DECLARE @s VARCHAR（MAX）='您的HTML在这里';
DECLARE @xml XML = CAST（@s AS XML）; - 你在这里得到第一个错误可能是
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）
结果为7，你喜欢它......
这条路
// h2 [@ id = sql：variable（@ H2_id）] /../../../ tr [2] / td / ul / li
$ b 读起来像这样：
lockquote
搜索任何h2中的任何h2具有与变量H2_id的内容相同的id-属性
的文档。现在移动三个级别
，然后取第二个< tr> ，然后向下移动到< td> code>，
< ul> 和< li> 。计算你在那里找到了多少。
看起来不错，但是
-
< h2> 可能是< H2> （XQuery区分大小写！
- HTML表可能使用
< the> 和< tfoot>
- 链中可能有
< div> - 标签
- 几乎可以确定！ - 您的真实世界 HTML不是有效的XML
- 以及其他更多原因，为什么这种方法很糟糕！
您应该真正转身寻找合适的工具！
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
解决方案
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!
这篇关于如何计算sql中两个节点之间的公共节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
