How to define recursive XML function?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor for demonstrating XML-valued user-defined recursive function.

-- T-SQL Recursive XML User-Defined Function

-- Create table for testing with SELECT INTO

USE tempdb;

GO

SELECT EmpID=EmployeeID, MgrID = ManagerID, EmpName=FirstName+' '+LastName

INTO Employee

FROM AdventureWorks.HumanResources.Employee e

INNER JOIN AdventureWorks.Person.Contact c

on e.ContactID = c.ContactID

GO

SELECT * FROM Employee WHERE MgrID=3

GO

/*

EmpID MgrID EmpName

11    3     Jossef Goldberg

9     3     Gail Erickson

4     3     Rob Walters

158   3     Dylan Miller

263   3     Ovidiu Cracium

267   3     Michael Sullivan

270   3     Sharon Salavaria

*/

-- Create scalar-valued XML function

CREATE FUNCTION EmpList(@MgrID int)

RETURNS XML WITH RETURNS NULL ON NULL INPUT

BEGIN RETURN

  (SELECT EmpID as "@EmpID", EmpName as "@name",

      CASE WHEN MgrID=@MgrID

      THEN dbo.EmpList(EmpID)

      END

   FROM dbo.Employee WHERE MgrID=@MgrID

   FOR XML PATH('Employee'), TYPE)

END

GO

-- Test XML UDF

SELECT dbo.EmpList(3)

GO

/*

<Employee EmpID="11" name="Jossef Goldberg" />

<Employee EmpID="9" name="Gail Erickson" />

<Employee EmpID="4" name="Rob Walters" />

<Employee EmpID="158" name="Dylan Miller">

  <Employee EmpID="79" name="Diane Margheim" />

  <Employee EmpID="114" name="Gigi Matthew" />

  <Employee EmpID="217" name="Michael Raheem" />

</Employee>

<Employee EmpID="263" name="Ovidiu Cracium">

  <Employee EmpID="5" name="Thierry D'Hers" />

  <Employee EmpID="265" name="Janice Galvin" />

</Employee>

<Employee EmpID="267" name="Michael Sullivan" />

<Employee EmpID="270" name="Sharon Salavaria" />

*/

-- Cleanup

DROP FUNCTION dbo.EmpList

DROP TABLE tempdb.dbo.Employee

GO