Solved

CTE, SQL Server, FOR XML

Posted on 2014-02-20
6
820 Views
Last Modified: 2014-02-21
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
Comment
Question by:Alyanto
  • 3
  • 3
6 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39874144
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
 
LVL 1

Author Comment

by:Alyanto
ID: 39876238
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39876449
So you order by Parent, DepartmentId.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Author Comment

by:Alyanto
ID: 39876555
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
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39876562
Then sort by:

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

Open in new window

0
 
LVL 1

Author Closing Comment

by:Alyanto
ID: 39877166
Cheers and thanks for you help, a perfect solution.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question