SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help me understand xml.nodes()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattt
Posting Yak Master

194 Posts

Posted - 10/15/2012 :  10:35:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/15/2012 :  10:44:55  Show Profile  Reply with Quote
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 - 10/15/2012 :  10:49:06  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000