Hi,
Here's a table with some xml.
create table @test (intRowId int, xmlMetadata xml)
insert into @test values
(1, '<item id="202577" type="bundle">
<items>
<item id="192852" />
<item id="192853" />
<item id="192854" />
</items>
</item>')
insert into @test values
(2, '<item id="202578" type="bundle">
<items>
<item id="243009" />
<item id="227192" />
<item id="227193" />
</items>
</item>')
What I need to do is perform one select which will get me the /item/@id for each row and the /item/items/item@id selection underneath it. So for the above table what I'd like to see is:
parentId childId
================
202577 192852
202577 192853
202577 192854
202578 243009
202578 227192
202578 227193
To do this, as far as I can see, I'm best off using the inbuilt t-sql nodes function. So I wrote this:
select i.xmlMetadata.value('(/item/@id)[1]', 'varchar(9)') as parentId,
t.c.value('(/item/items/item/@id)[1]', 'varchar(9)') as childId
from @test i
cross apply i.xmlMetadata.nodes('/item/items/item') t(c)
Expecting that the nodes() function would return a list of nodes corresponding to the Xpath, so by asking for t.c.value I'd get the contents of each node. However, what I actually got was this:
parentId childId
================
202577 202577
202577 202577
202577 202577
202578 202578
202578 202578
202578 202578
So the t.c.value seems to be harvesting data from /item/@id and not /item/items/item@id. What have I misunderstood here, and what do I need to do to get the results I desire?
Cheers,
Matt
Help me understand xml.nodes()
Posted - 10/15/2012 : 10:35:44
