<Requisitions>
  <Record>
    <EmployeeID>101</EmployeeID>
    <FirstName>Jane</FirstName>
    <LastName>Doe</LastName>
    <JobTitle><![CDATA[Accountant - Level I]]></JobTitle>
    <Languages>
      <language>English</language>
    </Languages>
  </Record>
  <Record>
    <EmployeeID>102</EmployeeID>
    <FirstName>John</FirstName>
    <LastName>Smith</LastName>
    <JobTitle><![CDATA[Business Analyst]]></JobTitle>
    <Languages>
      <language>English</language>
      <language>Spanish</language>
    </Languages>
  </Record>
  <Record>
    <EmployeeID>103</EmployeeID>
    <FirstName>Erin</FirstName>
    <LastName>Thopmson</LastName>
    <JobTitle><![CDATA[Database Administrator]]></JobTitle>
    <Languages>
      <language>French</language>
      <language>Greek</language>
      <language>Italian</language>
    </Languages>
  </Record>
</Requisitions>

-- Create table variables
declare @Employee table
    (EmployeeID int NOT NULL,
     FirstName varchar(30) NOT NULL,
     LastName varchar(30) NOT NULL,
     JobTitle varchar(50) NOT NULL)
--
declare @Language table
    (LanguageID int NOT NULL,
     LanguageName varchar(30) NOT NULL)
--
declare @EmployeeLanguage table
    (EmployeeID int NOT NULL,
     LanguageID int NOT NULL)
--
-- Populate table variables with sample data
insert into @Language values (1, 'English')
insert into @Language values (2, 'Spanish')
insert into @Language values (3, 'French')
insert into @Language values (4, 'Italian')
insert into @Language values (5, 'Greek')
--
insert into @Employee values (101, 'Jane', 'Doe', 'Accountant - Level I')
insert into @Employee values (102, 'John', 'Smith', 'Business Analyst')
insert into @Employee values (103, 'Erin', 'Thopmson', 'Database Administrator')
--
insert into @EmployeeLanguage values (101, 1)
insert into @EmployeeLanguage values (102, 1)
insert into @EmployeeLanguage values (102, 2)
insert into @EmployeeLanguage values (103, 3)
insert into @EmployeeLanguage values (103, 4)
insert into @EmployeeLanguage values (103, 5)
--
-- Render as XML
select 1 as Tag,
       NULL as Parent,
       NULL as 'Requisitions!1',
       NULL as 'Record!2!EmployeeID!element',
       NULL as 'Record!2!FirstName!element',
       NULL as 'Record!2!LastName!element',
       NULL as 'Record!2!JobTitle!cdata',
       NULL as 'Languages!3!language!element'
union
SELECT 2 as Tag,
       1 as Parent,
       NULL,
       EmployeeID, 
       FirstName,
       LastName,
       JobTitle,
       NULL
FROM @Employee
union all
select 3 as Tag,
       2 as Parent,
       NULL,
       e.EmployeeID,
       e.FirstName,
       e.LastName,
       e.JobTitle,
       l.LanguageName
from @Employee e
inner join @EmployeeLanguage el on e.EmployeeID = el.EmployeeID
inner join @Language l on el.LanguageID = l.LanguageID
order by 'Record!2!EmployeeID!element', 'Languages!3!language!element'
for xml explicit

  <Record>
    <EmployeeID>103</EmployeeID>
    <FirstName>Erin</FirstName>
    <LastName>Thopmson</LastName>
    <JobTitle><![CDATA[Database Administrator]]></JobTitle>
    <Languages>
      <language>French</language>
    </Languages>
    <Languages>
      <language>Greek</language>
    </Languages>
    <Languages>
      <language>Italian</language>
    </Languages>
  </Record>

SELECT 1 AS Tag,
   0 AS Parent,
   NULL AS [REQUISITIONS!1],
   NULL AS [RECORD!2!EMPLOYEEID!element],
   NULL AS [RECORD!2!FIRSTNAME!element],
   NULL AS [RECORD!2!LASTNAME!element],
   NULL AS [RECORD!2!JOBTITLE!cdata],
   NULL AS [LANGUAGES!3],
   NULL AS [LANGUAGE!4]
UNION ALL
SELECT 2 AS Tag,
   1 AS Parent,
   NULL,
   EmployeeID,
   FirstName,
   LastName,
   JobTitle,
   NULL,
   NULL
FROM @Employee
UNION ALL
SELECT 3 AS Tag,
   2 AS Parent,
   NULL,
   EmployeeID,
   NULL,
   NULL,
   NULL,
   NULL,
   NULL
FROM @Employee
UNION ALL
SELECT 4 AS Tag,
   3 as Parent,
   NULL,
   EL.EmployeeID,
   NULL,
   NULL,
   NULL,
   NULL,
   L.LanguageName
FROM @EmployeeLanguage EL
   INNER JOIN @Language L ON (EL.LanguageID = L.LanguageID)
ORDER BY [RECORD!2!EMPLOYEEID!element], [LANGUAGE!4]
FOR XML EXPLICIT

SELECT 
   E.EmployeeID AS EMPLOYEEID,
   E.FirstName AS FIRSTNAME,
   E.LastName AS LASTNAME,
   E.JobTitle AS JOBTITLE,
   (
      SELECT L.LanguageName AS '*'
      FROM @Language L 
         INNER JOIN @EmployeeLanguage EL ON (L.LanguageID = EL.LanguageID) 
      WHERE (EL.EmployeeID = E.EmployeeID)
      FOR XML PATH('LANGUAGE'), ROOT('LANGUAGES'), Type
   ) 
FROM @Employee E
FOR XML PATH('RECORD'), ROOT('REQUISITIONS')