DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE