Alyanto
asked on
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.
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.
ASKER
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.
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.
So you order by Parent, DepartmentId.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers and thanks for you help, a perfect solution.
http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/