• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 891
  • Last Modified:

CTE, SQL Server, FOR XML

I am trying to prepare some data for use with the command FOR XML EXPLICIT, I started by investigating this at Link and it seems simple enough.  I started to implement a nested CTE.  The code is below.

WITH Divi AS
(
      SELECT 0 AS 'Parent', di.DivisionID, di.DivisionNo,NULL AS 'DeptId', di.DivisionName, Null AS 'DepartmentNumber', CAST(NULL AS Varchar(50)) AS 'DepartmentName' from Division di
      UNION ALL
      SELECT dp.DivisionID AS 'Parent',NULL 'DivisionID' , NULL AS 'DivisionNo', dp.DeptId, Null AS 'DivisionName', dp.DepartmentNumber, dp.DepartmentName FROM Divi div  JOIN Department dp ON div.DivisionID = dp.DivisionID
)

SELECT * FROM Divi

It does the job of getting the data as one might expect.  The problem is I am unsure how to order it so that it would conform to the ordering seen in the link.
0
Alyanto
Asked:
Alyanto
  • 3
  • 3
1 Solution
 
ZberteocCommented:
You will have to add an element of hierarchy and use it to sort. This example might help:

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/
0
 
AlyantoAuthor Commented:
Unfortunately this does not answer my question but it does progress another area of the problem.  I would ultimately like the nesting of data to be output using the FOR XML EXPLICIT option.  This said the parent child relationships here need to be in order to ensure that the XML is in the correct order.
Sample XML
   <division name="One" id=1>
       <Department name="A" Id=1/>
       <Department name="B" Id=2/>
   </division>
<division name="Two" id=2>
       <Department name="C" Id=3/>
       <Department name="D" Id=4/>
   </division>

Sample table as expected
Parent    DivisionId   DepartmentId   DivisionName   DepartmentName    
NULL      1                 NULL                 One                   NULL
1             NULL          1                        NULL                 A
1             NULL          2                        NULL                 B
NULL      2                 NULL                 Two                   NULL
2             NULL          3                        NULL                 C
2             NULL          4                        NULL                 D

The noteable thing is the output order and the nesting is related.  Parent is an alias of Division Id found in the department table.
0
 
ZberteocCommented:
So you order by Parent, DepartmentId.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
AlyantoAuthor Commented:
Ordering was one of the first things I tried unfortunately the result is that all of the NULL values appear at the top as a group.  The rest do sort themselves as you might expect and want.
0
 
ZberteocCommented:
Then sort by:

...
ORDER BY
       ISNULL(Parent,DivisionId),
       DepartmentId

Open in new window

0
 
AlyantoAuthor Commented:
Cheers and thanks for you help, a perfect solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now