如何处理错误子查询返回多个值1这个例子?
问题描述:
如何处理错误子查询返回超过1个值这个例子?如何处理错误子查询返回多个值1这个例子?
declare @html xml = N'<div itemtype="http://schema.org/Product"><table width="100%" cellspacing="0" cellpadding="2"><tr><td><table><tr><td class="navigation" ><a href="index.php">Battery Adapter</a> > <a href="index.php/cPath/681">Laptop CPU Fan</a> > <a href="index.php/cPath/681_707">ASUS</a> ><a href="product_info.php/products_id/3851">A8 Series</a> </td><td></td></tr></table></td></tr><tr><td><table align="center" class="product-more" width="99%"><tr><td colspan="5" class="pageHeading" align="left">Please select your right product for A8 Series from the list below.</td></tr><tr><td width="117"><a href="product_info.php/products_id/3851/3921#product0"><img src="images/sma/FASF3.jpg" alt="Original Brand New ASUS F3, F8 Series CPU Cooling Fan -- GC054509VH-A 3-PinS" title=" Original Brand New ASUS F3, F8 Series CPU Cooling Fan -- GC054509VH-A 3-PinS " width="100" height="75" hspace="5" vspace="5"/></a> </td><td width="226">Original Brand New ASUS F3, F8 Series CPU Cooling Fan -- GC054509VH-A 3-PinS</td><td width="13"></td><td width="116" class="price_product">$10.96</td><td width="113"><font color="blue"><b/>In Stock</font></td></tr><tr><td width="117"><a href="product_info.php/products_id/3851/3921#product1"><img src="images/sma/FASF3J.jpg" alt="Original Brand New ASUS F3, F3J, A8 Series CPU Cooling Fan -- GC055515VH-A 4-PinS" title=" Original Brand New ASUS F3, F3J, A8 Series CPU Cooling Fan -- GC055515VH-A 4-PinS " width="100" height="75" hspace="5" vspace="5"/></a> </td><td width="226">Original Brand New ASUS F3, F3J, A8 Series CPU Cooling Fan -- GC055515VH-A 4-PinS</td><td width="13"></td><td width="116" class="price_product">$10.96</td><td width="113"><font color="blue"><b/>In Stock</font></td></tr></table></td></tr><tr><td style="font:bold 11px Verdana, Arial, Helvetica, sans-serif; color:#3D9D17;">This product has <font color="#0000FF">2 </font>types </td></tr><tr><td>&nbsp;</td></tr><tr><td><table width="98%" cellspacing="0" cellpadding="0"><tr><td class="pageHeading" height="30" align="center"><h1><b/><span itemprop="name"><a name="product0"></a>Original Brand New ASUS F3, F8 Series CPU Cooling Fan -- GC054509VH-A 3-PinS </span></h1></td></tr><tr><td class="welcome_index"><span itemprop="description"> Get your ASUS A8 Series Laptop Fan from battery-adapter.com today is free of charge for shipment. We supply high quality ASUS A8 Series Laptop CPU Fan with low price. We guarantee the ASUS A8 Series Laptop CPU Cooling Fan with a full three-months warranty from the date of purchase if the product(s) have any quality problem!</span></td></tr></table></td></tr><tr><td><div itemprop="offers" itemtype="http://schema.org/Offer"><table width="100%" cellspacing="0" cellpadding="0"><tr><td valign="top" class="main" width="31%" align="right"><table width="100%" cellpadding="2" cellspacing="1"><tr><td style="border-right:1px dashed; border-bottom:1px dashed;" width="48%" height="2" align="center"><table cellspacing="0" cellpadding="2"><tr><td align="center" class="smallText"></td></tr><tr><td align="center" class="smallText"><a href="https://www.battery-adapter.com//productimage.php?product=FASF3"><img src="images/mid/FASF3.jpg" alt="Original Brand New ASUS F3, F8 Series CPU Cooling Fan -- GC054509VH-A 3-PinS" title=" Original Brand New ASUS F3, F8 Series CPU Cooling Fan -- GC054509VH-A 3-PinS "/>Click image to enlarge</a><noscript><a href="https://www.battery-adapter.com/images/mid/FASF3.jpg" target="_blank"><img src="images/mid/FASF3.jpg" alt="Original Brand New ASUS F3, F8 Series CPU Cooling Fan -- GC054509VH-A 3-PinS" title=" Original Brand New ASUS F3, F8 Series CPU Cooling Fan -- GC054509VH-A 3-PinS "/>Click image to enlarge</a> </noscript></td></tr><tr><td align="center" class="price_product"><meta itemprop="priceCurrency" content="USD" /><span itemprop="price" content="10.96">$10.96</span></td></tr><tr><td align="center" style="font:bold 14px Verdana, Arial, Helvetica, sans-serif; color:#3D9D17;">Free Shipping</td></tr><tr><td align="center" style="font:bold 14px Verdana, Arial, Helvetica, sans-serif; color:#3D9D17;">Brand new,3 months warranty!</td></tr><tr><td align="center"></td></tr><tr><td align="center"><img src="images/ce.gif" alt="Authentication"/></td></tr></table></td><td valign="top" style="border-bottom:1px dashed;"><table width="100%" cellpadding="2" cellspacing="0"><tr><td valign="bottom" align="left" style=" font:bold 11px Verdana, Arial, Helvetica, sans-serif;color:#3B4F89; border-bottom:double #3B4F89;">ASUS A8 Series Laptop Accessory Information: </td></tr><tr><td class="main"><b/>Specification: Brand New ASUS F3, F8 Series CPU Cooling FANTested to be 100% working properly. <b/>Unit: PCS <b/>Type: Laptop CPU Fan<meta itemprop="itemCondition" itemtype="http://schema.org/OfferItemCondition" content="http://schema.org/NewCondition"/><b/>Condition: Brand New<b/>Warranty: 3 Months<b/>Power: 5V 0.38A<b/>Info: Size (mm): 57 x 57 x 10, Wire Length: 75mm, (3 wire)3-pin connector</td></tr> <tr><td class="main"><b/>Availability:&nbsp;<meta itemprop="availability" content="http://schema.org/InStock"/>In Stock</td></tr><tr><td class="main"><b/>Payment | Delivery:&nbsp;PayPal | HongKong Registered Air Mail With Tracking Number, Free&nbsp;&nbsp;<a class="resources-newproduct" href="product_info.php/products_id/3851/3921/vA8+Series#bottom" title="Jump to detail of payment and shipping">[Detail?]</a></td></tr><tr><td height="30" align="left" valign="middle"><a href="https://www.battery-adapter.com/product_info.php/products_id/3851/3921/vA8%2BSeries/action/buy_now/products_id/3851"><img src="includes/languages/english/images/buttons/button_in_cart.gif" alt="Add to Cart" title=" Add to Cart " width="93" height="24"/></a> </td></tr><tr><td><!-- tell_a_friend //--><table width="100%" cellspacing="0" cellpadding="1" class="infoBox"><tr><td><table width="100%" cellspacing="0" cellpadding="0" class="infoBoxContents"><tr><td><img src="images/pixel_trans.gif" alt="" width="100%" height="1"/></td></tr><tr><td align="left" class="boxText"><form name="tell_a_friend" action="https://www.battery-adapter.com/tell_a_friend.php" method="get"><a class="index_newlink" href="https://www.battery-adapter.com/product_reviews.php/products_id/3851"><img class="image_float" alt="View & Write Reviews" src="includes/languages/english/images/buttons/button_write_view.gif" /></a><a class="index_newlink" href="https://www.battery-adapter.com/ask_question.php/products_id/3851"><img class="image_float" alt="Ask a question" src="includes/languages/english/images/buttons/button_ask_question.gif" /></a></form></td></tr><tr><td><img src="images/pixel_trans.gif" alt="" width="100%" height="1"/></td></tr></table></td></tr></table><!-- tell_a_friend_eof //--></td></tr></table></td></tr></table></td></tr></table></div></td></tr><tr><td>&nbsp;</td></tr><tr><td> <div id="modelinfo0"><table width="99%" align="center" cellpadding="2" cellspacing="1" bgcolor="#F2E9D5"><tr><td colspan="2" class="replacement" height="25">&nbsp;<h2><b/>Compatible ASUS A8 Series Part Number and Models:</h2></td><td align="center"><a class="footerproduct" href="product_info.php/products_id/3851/3921/vA8+Series#brands0">Compatible Brands</a></td></tr><tr bgcolor="#FFFFFF"><td colspan="3" class="product_partno">Compatible Code</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">13.V1.B3564.F.GN</td><td width="33%" class="pro_in">GC054509VH-A</td><td width="33%"></td></tr><tr bgcolor="#FFFFFF"><td colspan="3" class="product_partno">Fit Model</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">A8 Series</td><td width="33%" class="pro_in">F3 Series</td><td width="33%" class="pro_in">F3T Series</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">F8 Series</td><td width="33%" class="pro_in">F8S Series</td><td width="33%" class="pro_in">F8SR Series</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">F8SV Series</td><td width="33%" class="pro_in">F8Sa Series</td><td width="33%" class="pro_in">F8VA Series</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">Z91V Series</td><td width="33%" class="pro_in">Z99J Series</td><td width="33%"></td></tr></table></div></td></tr><tr><td><table width="99%" align="center" cellpadding="2" cellspacing="1" bgcolor="#F2E9D5"><tr><td colspan="3" class="replacement" height="25"><a name="brands0" title="Compatible brands"></a><h2><b/>Compatible Brands (Select brand to view models)</h2></td></tr><tr bgcolor="#FFFFFF"><td class="pro_in"><a class="footerproduct" href="product_info.php/products_id/3851/3921/vA8+Series#modelinfo0" onClick="show_text(3851,modelinfo0);">ASUS(11)</a></td><td width="33%"></td><td width="33%"></td> </tr></table></td></tr><tr><td class="pageHeading"><h2><b/></h2></td></tr><tr><td class="welcome_index"></td></tr><tr><td></td></tr><tr><td><table width="98%" cellspacing="0" cellpadding="0"><tr><td class="pageHeading" height="30" align="center"><h1><b/><span itemprop="name"><a name="product1"></a>Original Brand New ASUS F3, F3J, A8 Series CPU Cooling Fan -- GC055515VH-A 4-PinS </span></h1></td></tr><tr><td class="welcome_index"><span itemprop="description"> Get your ASUS A8 Series Laptop Fan from battery-adapter.com today is free of charge for shipment. We supply high quality ASUS A8 Series Laptop CPU Fan with low price. We guarantee the ASUS A8 Series Laptop CPU Cooling Fan with a full three-months warranty from the date of purchase if the product(s) have any quality problem!</span></td></tr></table></td></tr><tr><td><div itemprop="offers" itemtype="http://schema.org/Offer"><table width="100%" cellspacing="0" cellpadding="0"><tr><td valign="top" class="main" width="31%" align="right"><table width="100%" cellpadding="2" cellspacing="1"><tr><td style="border-right:1px dashed; border-bottom:1px dashed;" width="48%" height="2" align="center"><table cellspacing="0" cellpadding="2"><tr><td align="center" class="smallText"></td></tr><tr><td align="center" class="smallText"><a href="https://www.battery-adapter.com//productimage.php?product=FASF3J"><img src="images/mid/FASF3J.jpg" alt="Original Brand New ASUS F3, F3J, A8 Series CPU Cooling Fan -- GC055515VH-A 4-PinS" title=" Original Brand New ASUS F3, F3J, A8 Series CPU Cooling Fan -- GC055515VH-A 4-PinS "/>Click image to enlarge</a><noscript><a href="https://www.battery-adapter.com/images/mid/FASF3J.jpg" target="_blank"><img src="images/mid/FASF3J.jpg" alt="Original Brand New ASUS F3, F3J, A8 Series CPU Cooling Fan -- GC055515VH-A 4-PinS" title=" Original Brand New ASUS F3, F3J, A8 Series CPU Cooling Fan -- GC055515VH-A 4-PinS "/>Click image to enlarge</a> </noscript></td></tr><tr><td align="center" class="price_product"><meta itemprop="priceCurrency" content="USD" /><span itemprop="price" content="10.96">$10.96</span></td></tr><tr><td align="center" style="font:bold 14px Verdana, Arial, Helvetica, sans-serif; color:#3D9D17;">Free Shipping</td></tr><tr><td align="center" style="font:bold 14px Verdana, Arial, Helvetica, sans-serif; color:#3D9D17;">Brand new,3 months warranty!</td></tr><tr><td align="center"></td></tr><tr><td align="center"><img src="images/ce.gif" alt="Authentication"/></td></tr></table></td><td valign="top" style="border-bottom:1px dashed;"><table width="100%" cellpadding="2" cellspacing="0"><tr><td valign="bottom" align="left" style=" font:bold 11px Verdana, Arial, Helvetica, sans-serif;color:#3B4F89; border-bottom:double #3B4F89;">ASUS A8 Series Laptop Accessory Information: </td></tr><tr><td class="main"><b/>Specification: Brand New ASUS F3, F3J, A8 Series CPU Cooling FANTested to be 100% working properly. <b/>Unit: PCS <b/>Type: Laptop CPU Fan<meta itemprop="itemCondition" itemtype="http://schema.org/OfferItemCondition" content="http://schema.org/NewCondition"/><b/>Condition: Brand New<b/>Warranty: 3 Months<b/>Power: DC5V 2.6W<b/>Info: Size(mm): 53 x 53 x 11, Wire Length: 65mm, (4 wires)4-pins connector</td></tr> <tr><td class="main"><b/>Availability:&nbsp;<meta itemprop="availability" content="http://schema.org/InStock"/>In Stock</td></tr><tr><td class="main"><b/>Payment | Delivery:&nbsp;PayPal | HongKong Registered Air Mail With Tracking Number, Free&nbsp;&nbsp;<a class="resources-newproduct" href="product_info.php/products_id/3851/3921/vA8+Series#bottom" title="Jump to detail of payment and shipping">[Detail?]</a></td></tr><tr><td height="30" align="left" valign="middle"><a href="https://www.battery-adapter.com/product_info.php/products_id/3851/3921/vA8%2BSeries/action/buy_now/products_id/3921"><img src="includes/languages/english/images/buttons/button_in_cart.gif" alt="Add to Cart" title=" Add to Cart " width="93" height="24"/></a> </td></tr><tr><td><!-- tell_a_friend //--><table width="100%" cellspacing="0" cellpadding="1" class="infoBox"><tr><td><table width="100%" cellspacing="0" cellpadding="0" class="infoBoxContents"><tr><td><img src="images/pixel_trans.gif" alt="" width="100%" height="1"/></td></tr><tr><td align="left" class="boxText"><form name="tell_a_friend" action="https://www.battery-adapter.com/tell_a_friend.php" method="get"><a class="index_newlink" href="https://www.battery-adapter.com/product_reviews.php/products_id/3921"><img class="image_float" alt="View & Write Reviews" src="includes/languages/english/images/buttons/button_write_view.gif" /></a><a class="index_newlink" href="https://www.battery-adapter.com/ask_question.php/products_id/3921"><img class="image_float" alt="Ask a question" src="includes/languages/english/images/buttons/button_ask_question.gif" /></a></form></td></tr><tr><td><img src="images/pixel_trans.gif" alt="" width="100%" height="1"/></td></tr></table></td></tr></table><!-- tell_a_friend_eof //--></td></tr></table></td></tr></table></td></tr></table></div></td></tr><tr><td>&nbsp;</td></tr><tr><td> <div id="modelinfo1"><table width="99%" align="center" cellpadding="2" cellspacing="1" bgcolor="#F2E9D5"><tr><td colspan="2" class="replacement" height="25">&nbsp;<h2><b/>Compatible ASUS A8 Series Part Number and Models:</h2></td><td align="center"><a class="footerproduct" href="product_info.php/products_id/3851/3921/vA8+Series#brands1">Compatible Brands</a></td></tr><tr bgcolor="#FFFFFF"><td colspan="3" class="product_partno">Compatible Code</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">13.B2239.F.GN</td><td width="33%" class="pro_in">DFB501005H20T(F7L8)</td><td width="33%" class="pro_in">GC055515VH-A</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">KFB0505HHA-W376</td><td width="33%"></td><td width="33%"></td> </tr><tr bgcolor="#FFFFFF"><td colspan="3" class="product_partno">Fit Model</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">A8 Series</td><td width="33%" class="pro_in">A8F</td><td width="33%" class="pro_in">A8Jm</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">A8Sc</td><td width="33%" class="pro_in">F3 Series</td><td width="33%" class="pro_in">F3J</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">M51 Series</td><td width="33%" class="pro_in">M51A Series</td><td width="33%" class="pro_in">M51E Series</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">M51Kr Series</td><td width="33%" class="pro_in">M51SN Series</td><td width="33%" class="pro_in">M51Se Series</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">M51Sr Series</td><td width="33%" class="pro_in">M51Va Series</td><td width="33%" class="pro_in">M51Vr Series</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">X53S Series</td><td width="33%" class="pro_in">Z53 Series</td><td width="33%" class="pro_in">Z53J Series</td></tr><tr bgcolor="#FFFFFF"><td width="34%" class="pro_in">Z53X Series</td><td width="33%" class="pro_in">Z99 Series</td><td width="33%"></td></tr></table></div></td></tr><tr><td><table width="99%" align="center" cellpadding="2" cellspacing="1" bgcolor="#F2E9D5"><tr><td colspan="3" class="replacement" height="25"><a name="brands1" title="Compatible brands"></a><h2><b/>Compatible Brands (Select brand to view models)</h2></td></tr><tr bgcolor="#FFFFFF"><td class="pro_in"><a class="footerproduct" href="product_info.php/products_id/3851/3921/vA8+Series#modelinfo1" onClick="show_text(3921,modelinfo1);">ASUS(20)</a></td><td width="33%"></td><td width="33%"></td> </tr></table></td></tr><tr><td class="pageHeading"><h2><b/></h2></td></tr><tr><td class="welcome_index"></td></tr><tr><td></td></tr><tr><td><table width="99%" align="right"><tr><td class="main"><font color="#af7120" size="2"><strong>Shipping Information:</strong></font>We ship to worldwide range and the shipping is also free unless you request express delivery. The delivery to US will take about 6-10 business days, and to the other most countries usually takes about 7~14 business days.Normally, all orders are shipped from * by HK post office registered air mail. You will get an email of shipping information including the tracking number once your order is shipped out from our warehouse.Please find the detail of the express as below:<table cellpadding="2" cellspacing="1" bgcolor="#CCCCCC" width="100%" class="main"><tr bgcolor="#FFFFFF"><td width="35%" class="main"><strong>Express Company</strong></td><td width="41%" class="main"><strong>Estimated Delivery Time</strong></td><td width="24%" class="main"><strong>Cost</strong></td></tr><tr bgcolor="#FFFFFF"><td class="main">EMS</td><td class="main">5-9 business days</td><td class="main">$18</td></tr><tr bgcolor="#FFFFFF" class="main"><td class="main">UPS</td><td class="main">3-6 business days</td><td class="main">$22</td></tr></table>If your address can"t be reached by above express company, please contact us by order&#64;battery-adapter.com and inform us of your express company.<a name="bottom"></a><font color="#af7120" size="2"><strong>About Payment:</strong></font><img src="images/paypal_accept.gif" alt="Paytype"/>We accept paypal paymentsNotice:You do not need to have a PayPal account to pay through Paypal.Don"t need to register a paypal account, Paypal accept the credit card directly now! Free, security and protect buyer.</td></tr></table></td></tr><tr><td class="pageHeading" ><h2><b/> &nbsp;<img src="images/heard_nav.gif" alt="Disclaimer"/> &nbsp;Disclaimer</h2></td></tr><tr><td class="main"><table><tr><td>&nbsp;</td><td class="main" style="color:#666666;">The products supplied by our Company are [replacement for] sold for use with certain products of computer manufacturers, and any reference to products or trademarks of such companies is purely for the purpose of identifying the computer manufacturers with which our products [are replacement for] may be used. Our Company and this Website are neither affiliated with, authorized by, licensed by, distributors for, nor related in any way to these computer manufacturers, nor are the products offered for sale through our Website manufactured by or sold with the authorization of the manufacturers of the computers with which our products [are replacement for] may be used.</td></tr></table></td></tr><tr><td align="right"><a href="product_info.php/products_id/3851/3921/vA8+Series#top" title="Jump to top of ASUS A8 Series Laptop CPU Fan | Replacement ASUS A8 Series Laptop Accessory page"><img src="images/top.gif" alt="Jump to top"/></a></td></tr></table></div>'
SP(与3产品的任何页面,并通过4产品的任何页):
declare @start as int
declare @end as int
set @start=(select ROW_Numbers from
(SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers
, t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[5]/td/div/table/tr/td') as t(v)
union
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers,
t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[8]/td/div/table/tr/td') as t(v)
union
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers,
t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[16]/td/div/table/tr/td') as t(v)
union
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers,
t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[24]/td/div/table/tr/td') as t(v)
)A
Where A.[Part Number ]='Compatible'
)
set @end=(select ROW_Numbers from
(SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers
, t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[5]/td/div/table/tr/td') as t(v)
union
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers,
t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[8]/td/div/table/tr/td') as t(v)
union
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers,
t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[16]/td/div/table/tr/td') as t(v)
union
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers,
t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[24]/td/div/table/tr/td') as t(v)
)A
Where A.[Part Number ]='Fit Model')
SELECT
A. PN as ComPart from
(SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers
, t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[5]/td/div/table/tr/td') as t(v))A
where A.ROW_Numbers>@start and A.ROW_Numbers<@end and A. PN!=''
union
SELECT
A. PN as ComPart from
(SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers
, t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[8]/td/div/table/tr/td') as t(v)
union
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers,
t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[16]/td/div/table/tr/td') as t(v)
union
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1000)) AS ROW_Numbers,
t.v.value('.','nvarchar(max)') as PN
FROM @htmlXML.nodes('div/table/tr[24]/td/div/table/tr/td') as t(v))A
where A.ROW_Numbers>@start and A.ROW_Numbers<@end and A. PN !=''
我的结果的任何页面只有1个PN和任何2页Pn和任何4页Pn和具有1个PN当第一tr是tr [5],但是当2 pn第一个tr是tr [8]等...
答
使用包含递归CTE的多个CTE。
WITH CTE AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn,
t.v.value('.','nvarchar(max)') as value
FROM @htmlXML.nodes('div/table/tr[position()=(5,8,16,24)]/td/div/table/tr/td') as t(v)
),
COMPATS AS (
select CTE.value as ComCode, min(FITMODEL.rn) as rn_fitmodel
from CTE
join (select rn from CTE where value = 'Fit Model') FITMODEL
on (FITMODEL.rn > CTE.rn)
where CTE.rn in (select rn+1 from CTE where value = 'Compatible Code')
GROUP BY CTE.value
),
RECURSIVE_CTE AS (
select ComCode, CTE.rn, CTE.value
from COMPATS
JOIN CTE ON (COMPATS.rn_fitmodel + 1 = CTE.rn)
UNION ALL
select RECURSIVE_CTE.ComCode, CTE.rn, CTE.value
from RECURSIVE_CTE
JOIN CTE ON (RECURSIVE_CTE.rn + 1 = CTE.rn AND CTE.value <> '')
)
SELECT ComCode, value as ComPart
FROM RECURSIVE_CTE
ORDER BY ComCode, rn;
你可以这样做'TR [位置()=(5,8,16,24)]',而不是工会的XPath的。你应该添加你期望的结果。因为我猜测一个'max(ROW_Numbers)'为变量获取单个值不是一个好的解决方案。 – LukStorms
thanks.test并回复我的朋友。请发送链接给我完整参考nodes.thanks – cyrus2500