TSQL CTE hierarchy

Question

I am trying to create a CTE on my table to pull in a hierarchy of employees.

I have a starting point which is a "Director" and I want to find everyone that reports to each person under them.

Here is what I have so far:

;WITH EmpTable_CTE (FirstName, LastName, QID, Email) AS
    (
        SELECT      FirstName,
                    LastName,
                    QID,
                    Email
        FROM        EmployeeTable E
        WHERE       QID = '12345'

        UNION ALL

        SELECT      E.FirstName,
                    E.LastName,
                    E.QID,
                    E.Email
        FROM        EmployeeTable E
        INNER JOIN  EmpTable_CTE AS E2 ON E.MgrQID = E2.QID
    )
    SELECT * FROM EmpTable_CTE

This seems to work providing me a list of employees but there is no "hierarchy" to it.

How can I go about using FOR XML to create the hierarchy that I am looking for?

<Director>Bob Smith</Director>
    <Direct>Jim Smith</Direct>
        <Direct>Employee 1</direct>
        <Direct>Employee 2</direct>
        <Direct>Employee 3</direct>
    <Direct>Bob Jones</Direct>
        <Direct>Employee 1</direct>
        <Direct>Employee 2</direct>
        <Direct>Employee 3</direct>
            <Direct>Employee A</direct>

I'm sure its just a matter of placing the FOR XML line somewhere but cant quite figure it out.

Update: Here is a SQL Fiddle of sample data:

http://sqlfiddle.com/#!6/a48f6/1

This is how I would expect the data to be from the fiddle:

<Director>Jim Jones</Director>
    <Direct>Bob Jones</Direct>
        <Direct>Jake Jones</Direct>
        <Direct>Smith Jones</Direct>
            <Direct>Carl Jones</Direct>
                <Direct>Bobby Jones</Direct>
    <Direct>Danny Jones</Direct>
        <Direct>Billy Jones</Direct>

The current output is just a table of results, it doesn't output the XML structure at all — SBB, Sep 30 '14 at 22:40
;WITH EmpTable_CTE (FirstName, LastName, QID, Email) AS ( SELECT FirstName, LastName, QID, Email FROM EmployeeTable E WHERE QID = 1 UNION ALL SELECT E.FirstName, E.LastName, E.QID, E.Email FROM EmployeeTable E INNER JOIN EmpTable_CTE AS E2 ON E.MgrQID = E2.QID ) SELECT LastName + ', ' + FirstName FROM EmpTable_CTE FOR XML PATH('Direct'), ROOT('Director'), TYPE — Bhasyakarulu Kottakota, Sep 30 '14 at 22:58
I really don't understand the sample output XML you're trying to achieve here. It's not hierarchical. — Codesleuth, Sep 30 '14 at 23:11

score 1 · Accepted Answer · edited May 23 '17 at 12:27

Part of the difficulty is in the XML structure you presented - If you passed that into a parser, it would all be flat, and running the results of my process below without stuffing the First and Last name into an attribute made the nodes come out in mixed content (text with nodes on the same level).

So, I went searching and found this little gem here on SE. Adapting it to your needs, and throwing in a few fields as attributes from your table, I came up with this:

CREATE FUNCTION dbo.EmpHierarchyNode(@QID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
  (SELECT QID AS "@ID", Email AS "@Email",
    FirstName + ' ' + LastName AS "@Name",
      CASE WHEN MgrQID = @QID
      THEN dbo.EmpHierarchyNode(QID)
      END
   FROM dbo.EmployeeTable
   WHERE MgrQID = @QID
   FOR XML PATH('Direct'), TYPE)
END

SELECT  QID AS "@ID", Email AS "@Email",
    FirstName + ' ' + LastName AS "@Name",
    dbo.EmpHierarchyNode(QID)
FROM dbo.EmployeeTable
WHERE MgrQID IS NULL
FOR XML PATH('Director'), TYPE

Essentially, this traverses down in the hierarchy, recursively calling itself. The CTE isn't sufficient if your output is targeted for XML. Using this, and what I could glean of your sample data, I got this as a result:

<Director ID="1" Email="bsmith@someCompany.com" Name="Bob Smith">
  <Direct ID="2" Email="jsmith@someCompany.com" Name="Jim Smith">
    <Direct ID="4" Email="e1@someCompany.com" Name="Employee 1" />
    <Direct ID="5" Email="e2@someCompany.com" Name="Employee 2" />
    <Direct ID="7" Email="e4@someCompany.com" Name="Employee 4" />
  </Direct>
  <Direct ID="3" Email="bjones@someCompany.com" Name="Bob Jones">
    <Direct ID="6" Email="e3@someCompany.com" Name="Employee 3" />
    <Direct ID="8" Email="e5@someCompany.com" Name="Employee 5" />
    <Direct ID="9" Email="e6@someCompany.com" Name="Employee 6" />
  </Direct>
</Director>

Hope this helps.

EDIT: Last Minute SQLFiddle Example.

score -1 · Answer 2 · answered Sep 30 '14 at 22:59

-1

See if it meets yours requirement:

;WITH EmpTable_CTE (FirstName, LastName, QID, Email) AS
    (
        SELECT      FirstName,
                    LastName,
                    QID,
                    Email
        FROM        EmployeeTable E
        WHERE       QID = 1
        UNION ALL
        SELECT      E.FirstName,
                    E.LastName,
                    E.QID,
                    E.Email
        FROM        EmployeeTable E
        INNER JOIN  EmpTable_CTE AS E2
        ON          E.MgrQID = E2.QID
    )

    SELECT LastName + ', ' + FirstName FROM EmpTable_CTE FOR XML PATH('Direct'), ROOT('Director'), TYPE

answered Sep 30 '14 at 22:59

Bhasyakarulu Kottakota

823
1
6
10

It does not, this just adds the tag around each of the names. – SBB Sep 30 '14 at 23:02
This would give only one level, looks like OP wants hierarchy base on levels – radar Sep 30 '14 at 23:02
In that case we have to use functions as like it mentioned over here http://stackoverflow.com/questions/14765937/cte-and-for-xml-to-generate-nested-xml – Bhasyakarulu Kottakota Sep 30 '14 at 23:07

TSQL CTE hierarchy

2 Answers2