• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 882
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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