在 T-SQL nodes() XQuery 中引用当前上下文

问题描述

是否可以通过基于第一个的上下文节点过滤第二个 nodes() 来实现两个 T-SQL nodes() 调用之间的连接?

Is it possible to effect a join between two T-SQL nodes() calls by filtering the second nodes() based on the context node of the first?

在下面的示例中，我试图返回一个显示 PersonID、Name 和 Position 的表.我尝试在第二个 nodes() XQuery 语句中引用第一个 nodes() 的上下文节点的 @id 不起作用.不返回职位名称.

In the example below, I'm trying to return a table showing PersonID, Name and Position. My attempt at referencing the @id of the first nodes()'s context node in the second nodes() XQuery statement doesn't work. No position names are returned.

有什么想法吗?

谢谢！

DECLARE @xml xml  = '<Root>
    <People>
        <Person id="1">Frank</Person>
        <Person id="2">Joe</Person>
    </People>
    <Positions>
        <Position assignedToPerson="1">Engineer</Position>
        <Position assignedToPerson="2">Manager</Position>
    </Positions>
</Root>'

SELECT
   PersonID = person.value('@id', 'NVARCHAR(50)'),
   Name = person.value('.', 'NVARCHAR(50)'),
   positionTitle = position.value('Position[1]', 'NVARCHAR(50)')
FROM
   @xml.nodes('/Root/People/Person') People(person)
OUTER APPLY
   @xml.nodes('/Root/Positions/Position[@assignedToPerson=.[@id]]') Positions(position)

推荐答案

使用按值连接而不是按节点交叉应用应该可行；投影中的 positiontitle 然后变成元素值 (.):

Using join by value instead of cross apply by node should work; positiontitle in the projection then becomes the element value (.):

DECLARE @xml xml  = '<Root>
    <People>
        <Person id="1">Frank</Person>
        <Person id="2">Joe</Person>
    </People>
    <Positions>
        <Position assignedToPerson="1">Engineer</Position>
        <Position assignedToPerson="2">Manager</Position>
    </Positions>
</Root>'

SELECT
   PersonID = person.value('@id', 'NVARCHAR(50)'),
   Name = person.value('.', 'NVARCHAR(50)'),
   positionTitle = position.value('.', 'NVARCHAR(50)')
FROM
   @xml.nodes('/Root/People/Person') People(person)
JOIN
   @xml.nodes('/Root/Positions/Position') Positions(position)
ON person.value('@id', 'NVARCHAR(50)')=
position.value('@assignedToPerson[1]','NVARCHAR(50)')

这篇关于在 T-SQL nodes() XQuery 中引用当前上下文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！