sql-server - 在SQL Server 2008中使用XPath/XQuery将一个属性与另一个属性进行匹配

Question

考虑一下XML和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)

我最终得到：

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

我想要的是基于当前 item的@id属性拉出的“注释”列。如果我将选择器中的 [@for=data(@id)]替换为 [@for=107]，我当然会在最后一条记录中得到值 Some notes!。是否可以使用XPath / XQuery做到这一点，还是我在这里树错了树？我认为问题是

XML有点尴尬，是的，但是恐怕我无法真正更改它。

我找到了一个可行的解决方案，但是对于这样的事情，它感觉非常沉重。

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')

更新！

我想到了！我是XQuery的新手，但是可以用，所以我称它为完成工作：)我将注释的查询更改为：

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

Answer 1

我建议您执行cross apply而不是执行../来找到父节点。根据查询计划，它要快得多。

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)

您甚至可以通过应用一次附加的交叉操作来删除flwor中的 ../，获得更多。

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) 

将查询相互比较，我得到的查询的67％是我的第一个查询的17％，第二个是16％。注意：这些数字仅给您提示实际上实际上哪些查询会更快。针对您的数据进行测试以确保确定。