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
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.