在 SQL Server 2008 中使用 XPath/XQuery 将一个属性与另一个属性匹配

考虑 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

我想要的是根据当前的 @id 属性提取“注释”列item。如果我更换[@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')

Update!

我想到了！我是 XQuery 的新手，但这很有效，所以我称其为“工作完成”:) 我将注释的查询更改为：

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

我建议你做一个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)

您甚至可以删除../在花中，再加上一个额外的交叉，可以获得更多的收益。

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%。注意：这些数字只是提示您实际上哪种查询实际上会更快。根据您的数据进行测试以确定结果。