mister.magoo (7/1/2014)

---

You can use a recursive CTE to generate a relationship table:

(Note: I could not get this to work with your schema collection though, for some reason it complains about the "value" method needing a singleton, but works fine without it)

WITH levels( Manager, Employee, Level, Node) AS

(

-- Anchor the query on the first employees node (Is this correct?)

SELECT cast(NULL AS varchar(100)) as Manager, nd.value('string((Name/@val)[1])','varchar(100)') AS Employee, 1 AS Level, nd.query('.') Node

FROM @x.nodes('(//employee)[1]') x(nd)

UNION ALL

-- Pull in all children that are employeeEntry nodes

SELECT levels.Employee, ed.value('(Name/@val)[1]','varchar(100)') AS Name, level+1 AS Level, ed.query('.')

FROM levels

CROSS APPLY levels.Node.nodes('child::node()/employeeEntry') x(ed)

UNION ALL

-- And all children that are employee/employeeEntry nodes

SELECT levels.Employee, ed.value('(Name/@val)[1]','varchar(100)') AS Name, level+1 AS Level, ed.query('.')

FROM levels

CROSS APPLY levels.Node.nodes('child::node()/employee/employeeEntry') x(ed)

)

SELECT Manager, Employee,Level

FROM levels

ORDER BY Level, Manager, Employee;