Matching one attribute to another using XPath/XQuery in SQL Server 2008

Consider the XML and SQL:

declare @xml xml = '
<root>
    <person id="11272">
        <notes for="107">Some notes!</notes>
        <item id="107" selected="1" />
    </person>
    <person id="77812">
        <notes for="107"></notes>
        <notes for="119">Hello</notes>
        <item id="107" selected="0" />
        <item id="119" selected="1" />
    </person>
</root>'

select  Row.Person.value('data(../@id)', 'int') as person_id,
        Row.Person.value('data(@id)', 'int') as item_id,
        Row.Person.value('data(../notes[@for=data(@id)][1])', 'varchar(max)') as notes
from    @xml.nodes('/root/person/item') as Row(Person)

I end up with:

person_id   item_id     notes
----------- ----------- -------
77812       107         NULL
77812       119         NULL
11272       107         NULL

What I want is the 'notes' column to be pulled based on the @id attribute of the current item. If I replace [@for=data(@id)] in the selector with [@for=107] of course I get the value Some notes! in the last record. Is it possible to do this with XPath/XQuery, or am I barking up the wrong tree here? I think the problem is that

The XML is a bit awkward, yes, but I can't really change it I'm afraid.

I found one solution that works, but it feels awfully heavy for something like this.

select  Item.Person.value('data(../@id)', 'int') as person_id,
        Item.Person.value('data(@id)', 'int') as item_id,
        Notes.Person.value('text()[1]', 'varchar(max)') as notes
from    @xml.nodes('/root/person/item') as Item(Person)
        inner join @xml.nodes('/root/person/notes') as Notes(Person) on
            Notes.Person.value('data(@for)', 'int') = Item.Person.value('data(@id)', 'int')
            and
            Notes.Person.value('data(../@id)', 'int') = Item.Person.value('data(../@id)', 'int')

Update!

I figured it out! I'm new at XQuery but this works, so I'm calling it job done :) I changed the query for the notes to:

Item.Person.value('
    let $id := data(@id)
    return data(../notes[@for=$id])[1]
', 'varchar(max)') as notes

Answers


I would suggest that you do a cross apply instead of doing ../ to find a parent node. According to query plan it is a lot faster.

select  P.X.value('data(@id)', 'int') as person_id,
        I.X.value('data(@id)', 'int') as item_id,
        I.X.value('let $id := data(@id)
                   return data(../notes[@for=$id])[1]', 'varchar(max)') as notes
from @xml.nodes('/root/person') as P(X)
  cross apply P.X.nodes('item') as I(X)

You can even remove the ../ in the flwor with one extra cross apply gaining a bit more.

select P.X.value('@id', 'int') as person_id,
       TI.id as item_id,
       P.X.value('(notes[@for = sql:column("TI.id")])[1]', 'varchar(max)') as notes
from @xml.nodes('/root/person') as P(X)
  cross apply P.X.nodes('item') as I(X)
  cross apply (select I.X.value('@id', 'int')) as TI(id) 

Comparing the queries against each other I got 67% on your query 17% on my first and 16% on the second. Note: these figures only give you a hint on what query will actually be faster in reality. Test the against your data to know for sure.


Need Your Help

yii dropDownList populated data dynamically with Javascript posting null values

php javascript jquery forms yii

How do I retrieve the value which is populating data dynamically with Javascript in yii framework? When I post the data it's inserting the database as null

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.