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 192852202577 192853202577 192854202578 243009202578 227192202578 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 childIdfrom @test icross 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 202577202577 202577202577 202577202578 202578202578 202578202578 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