Issue with nesting queries in sql query for xml explicit for multiple parent tags

Question

Using XML explicit query because customer needs me to use CDATA tags but having issue writing a nested xml for Research area.

 SELECT 
    1 AS Tag,
    0 AS Parent,
    NULL AS [row!1],
    NULL AS [Department!2!Department!Cdata],
    NULL AS [Job!3],
    NULL AS [Job!4!Jobtitle!CDATA],
    NULL AS [Job!4!Ranking],
    NULL AS [UserDetails!5!PersonalID!element],
    NULL AS [UserDetails!5!OverallRanking!element],
    NULL AS [UserDetails!5!Forename!element],
    NULL AS [UserDetails!5!Surname!element],
    NULL AS [ResearchArea!6!ResearchAreaName],
    NULL AS [ResearchArea!7!ResearchAreaName!CDATA]



UNION 

SELECT 
    2 AS Tag,
    1 AS Parent,
    NULL,
    Department,
    NULL,
    NULL,
    NULL,
    NULL,
NULL,
NULL,
NULL,
NULL,
null

FROM [dbo].[FACTUserProfile] FACT 
inner join [dbo].[UPDepartmentJob] UPJob on UPJob.UserProfileID=FACT.UserProfileID
INNER JOIN [dbo].[DepartmentJob] Job ON UPJob.DepJobID=Job.DepJobID
where FACT.ProfileStatusID=1
  UNION 
SELECT 
    3 AS Tag,
    2 AS Parent,
    NULL,
    Department,
    NULL,
    NULL,
    NULL,
    NULL,
NULL,
NULL,
NULL,
NULL,
null
FROM [dbo].[FACTUserProfile] FACT 
inner join [dbo].[UPDepartmentJob] UPJob on UPJob.UserProfileID=FACT.UserProfileID
INNER JOIN [dbo].[DepartmentJob] Job ON UPJob.DepJobID=Job.DepJobID
where FACT.ProfileStatusID=1

 UNION 

SELECT 
    4 AS Tag,
    3 AS Parent,
    NULL,
    Department,
    NULL,
    JobTitle,
    Ranking,
    null,
    NULL,
    NULL,
NULL,
NULL,
null
FROM [dbo].[FACTUserProfile] FACT 
inner join [dbo].[UPDepartmentJob] UPJob on UPJob.UserProfileID=FACT.UserProfileID
INNER JOIN [dbo].[DepartmentJob] Job ON UPJob.DepJobID=Job.DepJobID
INNER JOIN [dbo].UserDetails userdetails ON fact.UserDetailsID=userdetails.UserDetailsID
where FACT.ProfileStatusID=1

 UNION 

SELECT 
    4 AS Tag,
    3 AS Parent,
    NULL,
    Department,
    NULL,
    JobTitle,
    Ranking,
    null,
    NULL,
    NULL,
NULL,
NULL,
null
FROM [dbo].[FACTUserProfile] FACT 
inner join [dbo].[UPDepartmentJob] UPJob on UPJob.UserProfileID=FACT.UserProfileID
INNER JOIN [dbo].[DepartmentJob] Job ON UPJob.DepJobID=Job.DepJobID
INNER JOIN [dbo].UserDetails userdetails ON fact.UserDetailsID=userdetails.UserDetailsID
where FACT.ProfileStatusID=1

 UNION 

SELECT 
    5 AS Tag,
    4 AS Parent,
    NULL,
    job.Department,
    NULL,
    JobTitle,
    Ranking,
    userdetails.PersonalID,
    ROW_NUMBER() OVER(PARTITION BY job.Department ORDER BY Ranking, userdetails.Surname ASC, userdetails.Forename ASC) AS OverallRanking
    ,userdetails.[Forename]
    ,userdetails.[Surname]
    ,null
    ,null

FROM [dbo].[FACTUserProfile] FACT 
 inner join [dbo].[UPDepartmentJob] UPJob on UPJob.UserProfileID=FACT.UserProfileID
  INNER JOIN [dbo].[DepartmentJob] Job ON UPJob.DepJobID=Job.DepJobID
  inner join [dbo].[DepartmentContactDetails] DeptContact on DeptContact.DepartmentID=job.DepartmentID
  INNER JOIN [dbo].UserDetails userdetails ON fact.UserDetailsID=userdetails.UserDetailsID
  left join dbo.UPResearchArea UPRA on UPRA.UserProfileID=FACT.UserProfileID
  left JOIN [dbo].[ResearchArea] ResearchArea ON ResearchArea.RAreaID=UPRA.RAreaID
where FACT.ProfileStatusID=1

UNION 

SELECT 
    6 AS Tag,
    5 AS Parent,
    NULL,
    job.Department,
    NULL,
    JobTitle,
    Ranking,
    userdetails.PersonalID,
    ROW_NUMBER() OVER(PARTITION BY job.Department ORDER BY Ranking, userdetails.Surname ASC, userdetails.Forename ASC) AS OverallRanking
    ,userdetails.[Forename]
    ,userdetails.[Surname]
    ,null
    ,null

FROM [dbo].[FACTUserProfile] FACT 
 inner join [dbo].[UPDepartmentJob] UPJob on UPJob.UserProfileID=FACT.UserProfileID
  INNER JOIN [dbo].[DepartmentJob] Job ON UPJob.DepJobID=Job.DepJobID
  inner join [dbo].[DepartmentContactDetails] DeptContact on DeptContact.DepartmentID=job.DepartmentID
  INNER JOIN [dbo].UserDetails userdetails ON fact.UserDetailsID=userdetails.UserDetailsID
  left join dbo.UPResearchArea UPRA on UPRA.UserProfileID=FACT.UserProfileID
  left JOIN [dbo].[ResearchArea] ResearchArea ON ResearchArea.RAreaID=UPRA.RAreaID

where FACT.ProfileStatusID=1

UNION 

SELECT 
    7 AS Tag,
    5 AS Parent,
    NULL,
    job.Department,
    NULL,
    JobTitle,
    Ranking,
    userdetails.PersonalID,
    ROW_NUMBER() OVER(PARTITION BY job.Department ORDER BY Ranking, userdetails.Surname ASC, userdetails.Forename ASC) AS OverallRanking
    ,userdetails.[Forename]
    ,userdetails.[Surname]
    ,null
    ,ResearchArea.ResearchAreaName

FROM [dbo].[FACTUserProfile] FACT 
 inner join [dbo].[UPDepartmentJob] UPJob on UPJob.UserProfileID=FACT.UserProfileID
  INNER JOIN [dbo].[DepartmentJob] Job ON UPJob.DepJobID=Job.DepJobID
  inner join [dbo].[DepartmentContactDetails] DeptContact on DeptContact.DepartmentID=job.DepartmentID
  INNER JOIN [dbo].UserDetails userdetails ON fact.UserDetailsID=userdetails.UserDetailsID
  left join dbo.UPResearchArea UPRA on UPRA.UserProfileID=FACT.UserProfileID
  left JOIN [dbo].[ResearchArea] ResearchArea ON ResearchArea.RAreaID=UPRA.RAreaID

where FACT.ProfileStatusID=1

order by [Department!2!Department!Cdata], [Job!4!Ranking], [Job!4!Jobtitle!CDATA], [UserDetails!5!OverallRanking!element]
FOR XML EXPLICIT

When I execute this code it produces the following:

<row>
  <Department>
    <Department><![CDATA[Brass]]></Department>
    <Job>
      <Job Ranking="3">
        <Jobtitle><![CDATA[Head of Brass]]></Jobtitle>
        <UserDetails>
          <PersonalID>01881</PersonalID>
          <OverallRanking>1</OverallRanking>
          <Forename>Nigel</Forename>
          <Surname>Black</Surname>
          <ResearchArea />
          <ResearchArea>
            <ResearchAreaName><![CDATA[Psychology]]></ResearchAreaName>
          </ResearchArea>
        </UserDetails>
        <UserDetails>
          <PersonalID>01881</PersonalID>
          <OverallRanking>2</OverallRanking>
          <Forename>Nigel</Forename>
          <Surname>Black</Surname>
          <ResearchArea />
          <ResearchArea>
            <ResearchAreaName><![CDATA[Health & Wellbeing]]></ResearchAreaName>
          </ResearchArea>
        </UserDetails>
      </Job>

But I need to produce the following:

   <row>
      <Department>
        <Department><![CDATA[Brass]]></Department>
        <Job>
          <Job Ranking="3">
            <Jobtitle><![CDATA[Head of Brass]]></Jobtitle>
            <UserDetails>
              <PersonalID>01881</PersonalID>
              <OverallRanking>1</OverallRanking>
              <Forename>Nigel</Forename>
              <Surname>Black</Surname>
              <ResearchArea />
              <ResearchArea>
                <ResearchAreaName><![CDATA[Psychology]]></ResearchAreaName>
                <ResearchAreaName><![CDATA[Health & Wellbeing]]>   
              </ResearchAreaName>
              </ResearchArea>
            </UserDetails>
          </Job>

please can someone help? Please do not ask me to use other for xml clauses as I neeed CDATA tags for fields with html code in.

Many thanks, Lauren