Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server CTE and XML Recursion Script


By:   |   Read Comments   |   Related Tips: More > Scripts

Attend our free MSSQLTips Webcast - Understanding SQL Injection and the Consequences


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:

employee 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.

Query result with vpath of the data

 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:

SQL Server Recursion query result
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel has been working with SQL Server since 2007 as a Senior DBA.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools