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

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

SQL Server CTE and XML Recursion Script


By:   |   Last Updated: 2017-11-16   |   Comments (1)   |   Related Tips: 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:

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 Updated: 2017-11-16


next webcast button


next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, July 06, 2018 - 7:53:09 AM - Cristina Veloso Back To Top

 Thanks! It really helped!


Learn more about SQL Server tools