CODEWITHSUNDEEP
sqlxmlcdatafor-xmlfor-xml-explicit
Lauren Buxton
Lauren Buxton

Reputation: 95

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

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

Upvotes: 1

Views: 157

Answers (0)

Related Questions