By: Bhavesh Patel | Comments (1) | Related: More > Scripts
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:
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:
About the author
Bhavesh Patel is a SQL Server database professional with 10+ years of experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
