Referencing the Current Context in a T-SQL nodes() XQuery

Question

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?

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.

Any ideas?

Thank you!

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)

score 3 · Accepted Answer · answered Aug 01 '14 at 23:03

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

Thanks for your answer, @Jayvee! It doesn't look like what I hoped to do (reference the previous nodes() context from within a nodes() XQUERY) is possible. Your answer solves the larger need--joining the two document parts--so I'll award it the answer check. — Ben Gribaudo, Aug 05 '14 at 16:51

score 2 · Answer 2 · answered Aug 03 '14 at 05:24

Some minor changes here and there but pretty much same as what Jayvee has written. I have applied LEFT JOIN to ensure OUTER APPLY logic is met

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

SELECT   person.value('@id', 'INT') AS PersonID,
         person.value('.', 'NVARCHAR(50)') AS NAME,
         position.value('.', 'NVARCHAR(50)') AS positionTitle
FROM     @xml.nodes('/Root/People/Person') People(person)
LEFT JOIN    @xml.nodes('/Root/Positions/Position') Positions(position)
    ON   person.value('@id', 'INT') = position.value('@assignedToPerson', 'INT')

Referencing the Current Context in a T-SQL nodes() XQuery

2 Answers2

Linked