CTE and Hierarchical XML Result

Question

Hei,

I've a simple CTE that result with hierarchical list of items (Parent/Child relation). How can I convert it to XML with the "FOR XML" sql syntax while preserving the hierarchical structure?

Thanks, Espen

Answer 1

To build the hieararchy you need to "Stack" the query in a way that mimics the hierarchy you are trying to build. If you are using just one table or a CTE to build your XML you need to try and make the "parent" elements distinct within the sql calls. see how I used distinct when selecting parent below

Example:

use tempdb
GO
IF OBJECT_ID('tempdb..#ParentChild')IS NOT NULL DROP TABLE #ParentChild
CREATE TABLE #ParentChild(
ID              int identity(1,1),
ParentID        int,
ParentName      varchar(25),
ChildName       varchar(25));

Insert Into #ParentChild
Values(1,'John','Mike');

Insert Into #ParentChild
Values(1,'John','Russ');

Insert Into #ParentChild
Values(1,'John','Stan');

Select 
    pc.ParentName AS '@parent',
    (Select 
        p.ChildName as '@child'
     From #ParentChild p
     Where p.ParentID = pc.ParentID
     FOR XML PATH('children'),TYPE)
From (Select Distinct ParentID,ParentName
      From #ParentChild) pc
FOR XML PATH('parent'),TYPE

DROP TABLE #ParentChild;