이 예제에서 SQL의 동적 구문 분석 노드를 수행하는 방법은 무엇입니까?
declare @htmlXML as xml = N'
<div id="widget_featured_cats">
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-642522-clearance-hard-drives.html" title="Clearance Hard Drives"><img src="/images/F139947151" alt="Clearance Hard Drives" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-642522-clearance-hard-drives.html" title="Clearance Hard Drives">Clearance Hard Drives</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-1151766-laptop-parts-expert-clearance-laptops.html" title="Clearance Laptops"><img src="/images/F139445717" alt="Clearance Laptops" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-1151766-laptop-parts-expert-clearance-laptops.html" title="Clearance Laptops">Clearance Laptops</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-1136684-clearance-memory.html" title="Clearance Memory"><img src="/images/F136301574" alt="Clearance Memory" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-1136684-clearance-memory.html" title="Clearance Memory">Clearance Memory</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-1154955-laptop-accessories.html" title="Laptop Accessories"><img src="/images/F139947159" alt="Laptop Accessories" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-1154955-laptop-accessories.html" title="Laptop Accessories">Laptop Accessories</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48743-acer-notebook-repair-parts-accessories.html" title="Acer Laptop Parts"><img src="/images/F96258740" alt="Acer Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48743-acer-notebook-repair-parts-accessories.html" title="Acer Laptop Parts">Acer Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-49544-alienware-notebook-repair-parts-accessories.html" title="Alienware Laptop Parts"><img src="/images/F96258752" alt="Alienware Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-49544-alienware-notebook-repair-parts-accessories.html" title="Alienware Laptop Parts">Alienware Laptop Parts</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48744-apple-notebook-repair-parts-accessories.html" title="Apple Laptop Parts"><img src="/images/F96258741" alt="Apple Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48744-apple-notebook-repair-parts-accessories.html" title="Apple Laptop Parts">Apple Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-52775-asus-notebook-repair-parts-accessories.html" title="Asus Laptop Parts"><img src="/images/F96258754" alt="Asus Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-52775-asus-notebook-repair-parts-accessories.html" title="Asus Laptop Parts">Asus Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48745-averatec-notebook-repair-parts-accessories.html" title="Averatec Laptop Parts"><img src="/images/F96258742" alt="Averatec Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48745-averatec-notebook-repair-parts-accessories.html" title="Averatec Laptop Parts">Averatec Laptop Parts</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-52361-clevo-notebook-repair-parts-accessories.html" title="Clevo Laptop Parts"><img src="/images/F96258753" alt="Clevo Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-52361-clevo-notebook-repair-parts-accessories.html" title="Clevo Laptop Parts">Clevo Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48746-compaq-hp-notebook-repair-parts-accessories.html" title="Compaq Laptop Parts"><img src="/images/F96258743" alt="Compaq Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48746-compaq-hp-notebook-repair-parts-accessories.html" title="Compaq Laptop Parts">Compaq Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48747-dell-notebook-repair-parts-accessories.html" title="Dell Laptop Parts"><img src="/images/F96258744" alt="Dell Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48747-dell-notebook-repair-parts-accessories.html" title="Dell Laptop Parts">Dell Laptop Parts</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48776-emachines-notebook-repair-parts-accessories.html" title="eMachines Laptop Parts"><img src="/images/F96258751" alt="eMachines Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48776-emachines-notebook-repair-parts-accessories.html" title="eMachines Laptop Parts">eMachines Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48748-fujitsu-notebook-repair-parts-accessories.html" title="Fujitsu Laptop Parts"><img src="/images/F96258745" alt="Fujitsu Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48748-fujitsu-notebook-repair-parts-accessories.html" title="Fujitsu Laptop Parts">Fujitsu Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48749-gateway-notebook-repair-parts-accessories.html" title="Gateway Laptop Parts"><img src="/images/F96258746" alt="Gateway Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48749-gateway-notebook-repair-parts-accessories.html" title="Gateway Laptop Parts">Gateway Laptop Parts</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-329952-hp-notebook-repair-parts-accessories.html" title="HP Laptop Parts"><img src="/images/F96258756" alt="HP Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-329952-hp-notebook-repair-parts-accessories.html" title="HP Laptop Parts">HP Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48750-ibm-lenovo-notebook-repair-parts-accessories.html" title="Lenovo Laptop Parts"><img src="/images/F96258747" alt="Lenovo Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48750-ibm-lenovo-notebook-repair-parts-accessories.html" title="Lenovo Laptop Parts">Lenovo Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-187877-msi-notebook-repair-parts-accessories.html" title="MSI Laptop Parts"><img src="/images/F96258755" alt="MSI Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-187877-msi-notebook-repair-parts-accessories.html" title="MSI Laptop Parts">MSI Laptop Parts</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48752-panasonic-notebook-repair-parts-accessories.html" title="Panasonic Laptop Parts"><img src="/images/F96258748" alt="Panasonic Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48752-panasonic-notebook-repair-parts-accessories.html" title="Panasonic Laptop Parts">Panasonic Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-1065529-samsung-notebook-repair-parts-accessories.html" title="Samsung Laptop Parts"><img src="/images/F115567885" alt="Samsung Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-1065529-samsung-notebook-repair-parts-accessories.html" title="Samsung Laptop Parts">Samsung Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48753-sony-notebook-repair-parts-accessories.html" title="Sony Laptop Parts"><img src="/images/F96258749" alt="Sony Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48753-sony-notebook-repair-parts-accessories.html" title="Sony Laptop Parts">Sony Laptop Parts</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-48754-toshiba-notebook-repair-parts-accessories.html" title="Toshiba Laptop Parts"><img src="/images/F96258750" alt="Toshiba Laptop Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-48754-toshiba-notebook-repair-parts-accessories.html" title="Toshiba Laptop Parts">Toshiba Laptop Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-752780-acer-iconia-repair-parts-accessories.html" title="Acer Tablet Parts"><img src="/images/F73731670" alt="Acer Tablet Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-752780-acer-iconia-repair-parts-accessories.html" title="Acer Tablet Parts">Acer Tablet Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-409673-amazon-kindle-tablet-repair-parts-accessories.html" title="Amazon Tablet Parts"><img src="/images/F52994092" alt="Amazon Tablet Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-409673-amazon-kindle-tablet-repair-parts-accessories.html" title="Amazon Tablet Parts">Amazon Tablet Parts</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-752781-apple-ipad-tablet-repair-parts-accessories.html" title="Apple Tablet Parts"><img src="/images/F73731672" alt="Apple Tablet Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-752781-apple-ipad-tablet-repair-parts-accessories.html" title="Apple Tablet Parts">Apple Tablet Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-752782-asus-eee-pad-tablet-repair-parts-accessories.html" title="Asus Tablet Parts"><img src="/images/F73731673" alt="Asus Tablet Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-752782-asus-eee-pad-tablet-repair-parts-accessories.html" title="Asus Tablet Parts">Asus Tablet Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-752784-hp-slate-tablet-repair-parts-accessories.html" title="HP Tablet Parts"><img src="/images/F73731677" alt="HP Tablet Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-752784-hp-slate-tablet-repair-parts-accessories.html" title="HP Tablet Parts">HP Tablet Parts</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-752785-lenovo-ideapad-tablet-repair-parts-accessories.html" title="Lenovo Tablet Parts"><img src="/images/F73731741" alt="Lenovo Tablet Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-752785-lenovo-ideapad-tablet-repair-parts-accessories.html" title="Lenovo Tablet Parts">Lenovo Tablet Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-423727-motorla-xoom-tablet-repair-parts-accessories.html" title="Motorola Tablet Parts"><img src="/images/F54520996" alt="Motorola Tablet Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-423727-motorla-xoom-tablet-repair-parts-accessories.html" title="Motorola Tablet Parts">Motorola Tablet Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-768438-samsung-tablet-parts.html" title="Samsung Tablet Parts"><img src="/images/F75308431" alt="Samsung Tablet Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-768438-samsung-tablet-parts.html" title="Samsung Tablet Parts">Samsung Tablet Parts</a></div>
</div>
<hr style="clear: left;" />
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-752786-toshiba-thrive-tablet-repair-parts-accessories.html" title="Toshiba Tablet Parts"><img src="/images/F73731755" alt="Toshiba Tablet Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-752786-toshiba-thrive-tablet-repair-parts-accessories.html" title="Toshiba Tablet Parts">Toshiba Tablet Parts</a></div>
</div>
<div class="widget_fcats_box clearfix">
<div class="widget_fcats_image">
<a href="http://www.laptoppartsexpert.com/c-49492-clearance-laptop-notebook-parts.html" title="Clearance Laptop/Notebook Parts"><img src="/images/F96259130" alt="Clearance Laptop/Notebook Parts" /></a>
</div>
<div class="widget_fcats_title"><a href="http://www.laptoppartsexpert.com/c-49492-clearance-laptop-notebook-parts.html" title="Clearance Laptop/Notebook Parts">Clearance Laptop/Notebook Parts</a></div>
</div>
</div>';
SP :
SELECT t.v.value('.','nvarchar(max)') as [Name Of Brand],
--t.v.value('@title','nvarchar(max)') as [Title Of Brand],
t.v.value('@href','nvarchar(max)') as [URL Local],
t.v.value('@tppabs','nvarchar(max)') as [URL Site]
FROM @htmlXML.nodes('div/div[5]/div[2]/a') as t(v)
union ALL
SELECT t.v.value('.','nvarchar(max)') as [Name Of Brand],
--t.v.value('@title','nvarchar(max)') as [Title Of Brand],
t.v.value('@href','nvarchar(max)') as [URL Local],
t.v.value('@tppabs','nvarchar(max)') as [URL Site]
FROM @htmlXML.nodes('div/div[6]/div[2]/a') as t(v)
union ALL SELECT t.v.value('.','nvarchar(max)') as [Name Of Brand],
--t.v.value('@title','nvarchar(max)') as [Title Of Brand],
t.v.value('@href','nvarchar(max)') as [URL Local],
t.v.value('@tppabs','nvarchar(max)') as [URL Site]
FROM @htmlXML.nodes('div/div[7]/div[2]/a') as t(v)
union ALL SELECT t.v.value('.','nvarchar(max)') as [Name Of Brand],
--t.v.value('@title','nvarchar(max)') as [Title Of Brand],
t.v.value('@href','nvarchar(max)') as [URL Local],
t.v.value('@tppabs','nvarchar(max)') as [URL Site]
FROM @htmlXML.nodes('div/div[8]/div[2]/a') as t(v)
union ALL SELECT t.v.value('.','nvarchar(max)') as [Name Of Brand],
--t.v.value('@title','nvarchar(max)') as [Title Of Brand],
t.v.value('@href','nvarchar(max)') as [URL Local],
t.v.value('@tppabs','nvarchar(max)') as [URL Site]
FROM @htmlXML.nodes('div/div[11]/div[2]/a') as t(v)
union ALL SELECT t.v.value('.','nvarchar(max)') as [Name Of Brand],
--t.v.value('@title','nvarchar(max)') as [Title Of Brand],
t.v.value('@href','nvarchar(max)') as [URL Local],
t.v.value('@tppabs','nvarchar(max)') as [URL Site]
FROM @htmlXML.nodes('div/div[12]/div[2]/a') as t(v)
union ALL SELECT t.v.value('.','nvarchar(max)') as [Name Of Brand],
--t.v.value('@title','nvarchar(max)') as [Title Of Brand],
t.v.value('@href','nvarchar(max)') as [URL Local],
t.v.value('@tppabs','nvarchar(max)') as [URL Site]
FROM @htmlXML.nodes('div/div[14]/div[2]/a') as t(v)
union ALL SELECT t.v.value('.','nvarchar(max)') as [Name Of Brand],
--t.v.value('@title','nvarchar(max)') as [Title Of Brand],
t.v.value('@href','nvarchar(max)') as [URL Local],
t.v.value('@tppabs','nvarchar(max)') as [URL Site]
FROM @htmlXML.nodes('div/div[15]/div[2]/a') as t(v)
산출:
내 출력에는 특수한 수의 div가 포함됩니다. 예를 들어 : 2, 3,5,8,12,19,21 dynamic number div 왜냐하면 다른 페이지에는 div wit num = 21이 없기 때문일 수 있습니다.
하나의 선택으로 레코드를 테이블 변수 또는 임시 테이블에 삽입 할 수 있습니다.
declare @VarTable table (id int identity(1,1) primary key, [Title] varchar(max), [URL Local] varchar(max), [URL Site] varchar(max));
insert into @VarTable ([Title], [URL Local], [URL Site])
SELECT
t.v.query('div[@class="widget_fcats_title"][1]').value('.','nvarchar(max)') as [Title],
t.v.query('div[@class="widget_fcats_title"][1]').value('div[1]/a[1]/@href','nvarchar(max)') as [URL Local],
t.v.query('div[@class="widget_fcats_title"][1]').value('div[1]/a[1]/@tppabs','nvarchar(max)') as [URL Site]
FROM @htmlXML.nodes('//div[@class="widget_fcats_box clearfix"]') as t(v);
그런 다음 필요한 것을 선택하십시오.
예를 들면 :
declare @nodenumbers table (num int primary key);
insert into @nodenumbers (num) values (2),(3),(5),(8),(12),(19),(21);
select Title, [URL Local], [URL Site] from @VarTable where id in (select num from @nodenumbers);
또는:
select Title, [URL Local], [URL Site] from @VarTable where [URL Local] like '%-notebook-%';
사이드 노트로. 실제로 xquery를 사용하여 특정 수의 노드 위치 만 반환 할 수 있습니다. 이 경우에는 nodes ()는 리터럴을 원하는 것에 대해 엄격하기 때문에 도움이되지 않습니다.
예를 들면 :
FROM @htmlXML.nodes('//div[@class="widget_fcats_box clearfix"][position() = (2,3,5,8,12,19,21)]') as t(v);
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다