-- TEST DATA--------------------
create TABLE #tmp 
(
   EmployeeID int, 
   EmployeeName VARCHAR(255),
   EmployeeTypeID int,
   EmployeeSupervisorID int, 
   EmployeeSupervisorName VARCHAR(255)
);

INSERT INTO #tmp VALUES (10,'Joseph',3,1,'Chris');
INSERT INTO #tmp VALUES (11,'John',1,1,'Chris');


-- QUERY --------------------
WITH A AS -- because of the way the elements need to be nested, we need distinct supervisors
(
   SELECT DISTINCT 
      EmployeeSupervisorID,
      EmployeeSupervisorName
   FROM
      #tmp
)
SELECT -- creating EmployeeSupervisor element.
   a.EmployeeSuperVisorId AS [EmployeeSuperVisorId],
   a.EmployeeSupervisorName AS [EmployeeSupervisorName],
   (
      SELECT -- nesting employees element for each supervisor.
      (  
         SELECT -- nesting the employees under employees element
             
             b.EmployeeId AS [EmployeeID],
             b.EmployeeName AS [EmployeeName],
             b.EmployeeTypeID AS [EmployeeTypeID]
         FROM
             #tmp b
         WHERE
             a.EmployeeSuperVisorId = b.EmployeeSuperVisorId
         FOR XML PATH('Employee'),TYPE
      ) AS [Employees] FOR XML PATH(''), TYPE
   ) 
FROM
   a
FOR XML PATH('EmployeeSupervisor'),ROOT('EmployeeSupervisors');

-- CLEANUP----------------------
drop TABLE #tmp