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



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)

No comments:

Post a Comment