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