SQL Server 2005: Recursive Hierarchies to XML

Suppose we have a recursive hierarchy stored in a relational database and we want to write it to XML.  This might be for a variety of reasons – e.g. as a pre-cached input to a UI control, to export to another system using a pre-defined format, etc.
 
In SQL Server 2000, in order to get it straight to XML using FOR XML EXPLICIT, we would have to know the depth of the lowest node beforehand (without doing some very ugly dynamic SQL), so this does not help us.
 
It would be useful to access the data in the same order that it will appear in the XML.  I.e.
Node1
Node2
Node3
Node4
Node5
 
Getting at the data in this order will allow us to iterate through the nodes in sequential order.  This avoids using the DOM and is significantly quicker and more efficient as it avoids loading the whole structure into memory.
 
We could achieve this in SQL Server 2000 using a recursive table-valued UDF.  In SQL Server 2005, we also have the option of using a recursive Common Table Expression (CTE) to achieve the same functional result.  Let’s compare the two ways of doing it.
 
A CTE is a temporary named resultset referenced by a subsequent “outer query”.  They can provide similar functionality to views and derived tables, but their real value is in recursive queries.  Recursive CTE’s contain an “anchor member” and a “recursive member”, which are connected by a UNION ALL operator.  They can be encapsulated by UDFs for reusability.
 
 
 
Data Preparation
 
Let’s create a table and insert hierarchical values.
 
CREATE TABLE Employees
(
  empid   int         NOT NULL,
  mgrid   int         NULL,
  empname varchar(25) NOT NULL,
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT F