在SQL Server 2008中使用XPath/XQuery将一个属性与另一个属性进行匹配 [英] Matching one attribute to another using XPath/XQuery in SQL Server 2008

问题描述

考虑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做到这一点，还是我在这里树错了树?我认为问题是

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

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

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

更新！

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

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

推荐答案

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

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)

您甚至可以通过添加一个额外的叉号来删除fwor中的../.

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) 

将查询彼此进行比较，我在查询中得到67％，第一查询为17％，第二查询为16％.注意:这些数字仅提示您实际上实际上哪些查询会更快.对您的数据进行测试以确保确定.

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.

这篇关于在SQL Server 2008中使用XPath/XQuery将一个属性与另一个属性进行匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！