SQL Server CTE and XML Recursion Script

Problem

In SQL Server we normally use a Common Table Expression (CTE) for recursion data. In my scenario, I need to get every parent node against each child node. How can we solve this problem in SQL Server using a CTE and XML?

Solution

Let’s start off with an example to demonstrate the CTE and XML solution.

Here is an example of the mapping for my employee hierarchy:

ManagerA > (reports to no one)
ManagerB > (reports to no one)
ManagerRA > ManagerA
ManagerRB > ManagerA
ManagerRC > ManagerB
ManagerRD > ManagerB
ManagerRLA > ManagerRA > ManagerA
ManagerRLB > ManagerRB > ManagerA
ManagerRLC > ManagerRC > ManagerB
ManagerRLD > ManagerRD > ManagerB

Below is a script for an employee table and sample data to run in your test environment.

 CREATE TABLE employee
(
 id INT,
 empName NVARCHAR(200),
 designation VARCHAR(50),
 fk_parentEMP INT
)
GO
INSERT INTO employee
SELECT 1,'ManagerA','GM',0
UNION ALL
SELECT 2,'ManagerB','GM',0
UNION All
SELECT 3,'ManagerRA','RM',1
UNION ALL
SELECT 4,'ManagerRB','RM',1
UNION All
SELECT 5,'ManagerRC','RM',2
UNION ALL
SELECT 6,'ManagerRD','RM',2
UNION All
SELECT 7,'ManagerRLA','LM',3
UNION ALL
SELECT 8,'ManagerRLB','LM',4
UNION All
SELECT 9,'ManagerRLC','LM',5
UNION ALL
SELECT 10,'ManagerRLD','LM',6
GO

Now the employee table has been created and here is our mapping. The values in the parentheses show the map path to the ID column in the table.

ManagerA > (reports to no one) = (1 > 0)
ManagerB > (reports to no one) = (2 > 0)
ManagerRA > ManagerA = (3 > 1)
ManagerRB > ManagerA = (4 > 1)
ManagerRC > ManagerB = (5 > 2)
ManagerRD > ManagerB = (6 > 2)
ManagerRLA > ManagerRA > ManagerA = (7 > 3 > 1)
ManagerRLB > ManagerRB > ManagerA = (8 > 4 > 1)
ManagerRLC > ManagerRC > ManagerB = (9 > 5 > 2)
ManagerRLD > ManagerRD > ManagerB = (10 > 6 > 2)

This is what the data looks like in the table:

Here is the expected output that has the mapping that we need:

Required output for SQL Server Recursion

So how would I build a query to produce these results?

SQL Server CTE and XML Recursion Script

First I will prepare a query using a CTE for recursion:

 ;WITH EmployeeCTE 
   AS (
      SELECT 
         CAST(ISNULL(em.id,'0') AS VARCHAR(MAX)) AS vPath,
         em.id,
         em.empName AS empName,
         ISNULL(em.fk_parentEMP,'0') fk_parentEMP,
         em.Designation
      FROM dbo.EMPLOYEE (NOLOCK) em
      WHERE ISNULL(em.fk_parentEMP, 0) = 0
      UNION ALL
      SELECT   
         CAST(ee.vPath + '/'  + CAST( em.id AS VARCHAR(12)) AS VARCHAR(MAX))AS vPath,
         em.id,
         em.empName AS empName ,
         ISNULL(em.fk_parentEMP,'0') fk_parentEMP,
         em.Designation
      FROM dbo.EMPLOYEE (NOLOCK) em
      INNER JOIN EmployeeCTE ee ON  ee.id = em.fk_parentEMP
      )
SELECT * FROM EmployeeCTE

The vPath column shows the entire path for the each position. For the ManagerA and ManagerB since they do not report to anyone I am showing that the vPath is just their ID. For the additional positions, the vPath matches the information above in the mapping of each position.

Below is the final query to convert the vPath values to the output we want.

 ;WITH EmployeeCTE 
   AS (
      SELECT
         CAST(ISNULL(em.id,'0') AS VARCHAR(MAX)) AS vPath,
         em.id,
         em.empName AS empName,
         ISNULL(em.fk_parentEMP,'0') fk_parentEMP,
         em.Designation
      FROM dbo.EMPLOYEE (NOLOCK) em
      WHERE ISNULL(em.fk_parentEMP, 0) = 0
      UNION ALL
      SELECT
         CAST(ee.vPath + '/'  + CAST( em.id AS VARCHAR(12)) AS VARCHAR(MAX)) AS vPath,
         em.id,
         em.empName AS empName ,
         ISNULL(em.fk_parentEMP,'0') fk_parentEMP,
         em.Designation
      FROM dbo.EMPLOYEE (NOLOCK) em
      INNER JOIN EmployeeCTE ee ON  ee.id = em.fk_parentEMP
      )
   
SELECT
   id,
   empName,
   fk_parentEMP,
   Designation
FROM
(
   SELECT
      edm.id id,
      edm.empName,
      edm.fk_parentEMP,
      vPath,
      Designation
   FROM EmployeeCTE edm
   WHERE ISNULL(edm.fk_parentEMP,'0')  = '0'
   UNION
   SELECT
      id AS id,
      empName,
      tra.a.value('.', 'varchar(50)') AS  fk_parentEMP,
      vPath,
      Designation
   FROM (SELECT CAST('<Emp><Child>' + REPLACE(vPath, '/', '</Child><Child>') + '</Child></Emp>' AS XML) AS list ,
                id,
                empName,
                fk_parentEMP,
                vPath,
                Designation
         FROM EmployeeCTE
        ) a
   CROSS APPLY LIST.nodes('/Emp/Child') AS tra ( A )
) t1
WHERE t1.id <> t1.fk_parentEMP
ORDER BY vPath ASC

Now I am able to generate the result as expected:

Next Steps

Adapt this script based on the recursion needs in your environment.
Check out these resources:

Bhavesh Patel

Bhavesh Patel is a SQL Server database professional with 10+ years of experience. Most of his career has been working on database development and administration. He has a strong interest in continuous learning and likes facing challenges to learn new things. In his spare time, he spends time with his family. Currently, his main role is query tuning with respect to optimization and server performance.

MSSQLTips Awards: Trendsetter (25+ tips) – 2019 | Rookie of the Year Contender – 2017

SQL Server CTE and XML Recursion Script

Leave a ReplyCancel Reply