cyrus2500 cyrus2500 - 1 month ago 8
SQL Question

how to do dynamic parsing node in this example in sql?

how to do dynamic parsing node in this example in 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)


output:

my output contain special number of divs. as examp: 2 ,3,5,8,12,19,21
dynamic number div because maybe in other page there are not div wit num = 21

Answer

You could insert records with 1 select into a table variable, or a temporary table.

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);

Then select the things you need from it.

For example:

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);

or:

select Title, [URL Local], [URL Site] from @VarTable where [URL Local] like '%-notebook-%';

As a sidenote. It's actually possible to use xquery to only return a specific number of node positions. Won't help in this case though, since nodes() is strict about wanting a literal.
For example:

FROM  @htmlXML.nodes('//div[@class="widget_fcats_box clearfix"][position() = (2,3,5,8,12,19,21)]') as t(v);
Comments