在 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 属性提取“notes”列。如果我用 [@for=107] 替换选择器中的 [@for=data(@id)] ，我当然会得到值 Some Notes!< /code> 在最后一条记录中。是否可以使用 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)

您甚至可以删除 flw 中的 ../，并通过额外的交叉应用获得更多效果。

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