Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help me understand xml.nodes()

Author  Topic 

mattt
Posting Yak Master

194 Posts

Posted - 2012-10-15 : 10:35:44
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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 10:44:55
When you start with a / in your xpath, it goes up all the way back to the parent node. Instead, your query should be this
select i.xmlMetadata.value('(/item/@id)[1]', 'varchar(9)') as parentId,
t.c.value('(@id)[1]', 'varchar(9)') as childId
from #test i
cross apply i.xmlMetadata.nodes('/item/items/item') t(c)

Or, if you want to be more explicit:
select i.xmlMetadata.value('(/item/@id)[1]', 'varchar(9)') as parentId,
t.c.value('(./@id)[1]', 'varchar(9)') as childId
from #test i
cross apply i.xmlMetadata.nodes('/item/items/item') t(c)

Go to Top of Page

mattt
Posting Yak Master

194 Posts

Posted - 2012-10-15 : 10:49:06
Brilliant thanks!

So the fault is in my misunderstanding of XPath and not the SQL. This doesn't surprise me. I've found the documentation awful :)
Go to Top of Page
   

- Advertisement -