How many times have you been asked to generate a report that shows who reports to who? Pretty easy right, just a self-join T-SQL provided you have a manager ID key matching an employee ID key. But, while that's good, the user really wanted to see the whole org, in document, showing the full and correctly nested hierarchy. That becomes really ugly, pretty quick, because you need to use recursion. And, ok, you can do recursion with a Common Table Expression in SQL Server 2005 but... since XML is a natural representational form for that data, you can't because FOR XML can't be used in the either the anchor or the recursive part of a CTE. So you need function that both recurses and generates XML? There is a solution, and it looks like the following:
use adventureworks
go
alter function dbo.udfGetEmployeeContactXML
(
@managerID int
)
returns xml
with called on null input
as
begin
SET ANSI_NULLS OFF
declare @result xml
set @result = (
select e.EmployeeID as 'id'
, c.FirstName as 'name/givenPrimary'
, c.MiddleName as 'name/givenOther'
, c.LastName as 'name/family'
, e.Title as 'org/tile'
, d.[name] as 'org/department'
, d.GroupName as'org/group'
, c.Phone as 'contact/phone'
, c.EmailAddress as 'contact/email'
, a.City as 'geo/locale'
, sp.Name as 'geo/region'
, cr.Name as 'geo/country'
, dbo.udfGetEmployeeContactXML(e.employeeID)
from HumanResources.Employee e
join person.contact c
on e.contactID=c.contactID
join HumanResources.EmployeeDepartmentHistory edh
on e.EmployeeID=edh.employeeID and edh.enddate is null
join HumanResources.Department d
on edh.DepartmentID = d.departmentID
join HumanResources.EmployeeAddress ea
on e.employeeID = ea.employeeID
join Person.Address a
on ea.addressID = a.addressID
join Person.StateProvince sp
on a.StateProvinceID = sp.StateProvinceID
join Person.CountryRegion cr
on sp.CountryRegionCode = cr.CountryRegionCode
where e.managerID = @managerID
for xml path('employee'),type
)
return @result as nvarchar(max)
end
go
select dbo.udfGetEmployeeContactXML(null)
go