Solution to TSQL Beginners Challenge 17

DECLARE @Employees TABLE(EmpRecord XML)
INSERT INTO @Employees 
SELECT '
		
		
		
		
		
					
		
		
		
		
		'

DECLARE @EmpID VARCHAR(20)
SELECT @EmpID = 9

;WITH MyCte (EmpId, EmpName, ReportsTo, Path, NCount) AS
(
	SELECT 
		Emp.Node.value('@EmpId', 'nvarchar(50)') EmpId,
		Emp.Node.value('@EmpName', 'nvarchar(50)') EmpName,
		Emp.Node.value('@ReportsTo', 'nvarchar(50)') ReportsTo,
		CAST(Emp.Node.value('@EmpName', 'nvarchar(50)') AS VARCHAR(50)) AS Path,
		0 AS NCount
	FROM  @Employees CROSS APPLY EmpRecord.nodes('//Employees/Employee') AS Emp(Node)
	WHERE
		Node.value('@EmpId', 'nvarchar(50)') = @EmpID
	UNION ALL
	SELECT 
		Emp.Node.value('@EmpId', 'nvarchar(50)'),
		Emp.Node.value('@EmpName', 'nvarchar(50)') ,
		Emp.Node.value('@ReportsTo', 'nvarchar(50)'),
		CAST(Path  +  '/' + Emp.Node.value('@EmpName', 'nvarchar(50)') AS VARCHAR(50)),
		NCount + 1
	FROM 
		@Employees CROSS APPLY EmpRecord.nodes('//Employees/Employee') AS Emp(Node) 
		JOIN MyCte M ON m.ReportsTo = Emp.Node.value('@EmpId', 'nvarchar(50)')
)
SELECT
	Row_Number() OVER(ORDER BY NCount DESC) - 1 AS Level,
	Path AS Path,
	Space(Row_Number() OVER(ORDER BY NCount DESC) - 1) + EmpName AS Hierarchy
FROM
	MyCte

Tags: