Here's one approach. I adapted some code from another XML generation solution, and it might be a little messy. One difference is that here you have more of a many-to-many relationship, so nodes are duplicated where they have multiple parents.
Recursion is handled by a function which generates child nodes.
-- This SQL script drops and creates objects: only use in an empty test/development database.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RecursiveGroupFinding]') AND type in (N'U'))
DROP TABLE [RecursiveGroupFinding]
GO
CREATE TABLE RecursiveGroupFinding
(ParentIDINTNULL,
ChildIDINTNOT NULL
)
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[GroupList]') AND type in (N'U'))
DROP TABLE [dbo].[GroupList]
GO
CREATE TABLE GroupList
(GroupIDINTIDENTITY(1,1) NOT NULL,
GroupNameVARCHAR(30) NOT NULL
)
GO
CREATE CLUSTERED INDEX idx_c_RecursiveGroupFinding ON RecursiveGroupFinding (ParentID, ChildID)
CREATE CLUSTERED INDEX idx_c_GroupList ON GroupList (GroupID)
INSERT INTO GroupList
VALUES ('Parent1'),('Parent2'),('Child1'),('Child2'),('Child3'),('SubChild1'),('Subchild2'),('Subchild3'),('Icing')
INSERT INTO RecursiveGroupFinding
VALUES (NULL, 1), (NULL, 2), ( 1, 3), (1, 4), (2, 3),(2,5), (3, 6), (3, 7), (4, 8), (6, 9), (7,9)
GO
-- Function to create child nodes; uses recursion and a stopper.
--CREATE FUNCTION dbo.NodeBuilder2
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[NodeBuilder2]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION NodeBuilder2
GO
CREATE FUNCTION NodeBuilder2
(@BelongsTo SMALLINT)
RETURNS XML
AS
BEGIN
DECLARE @Node XML
SET @Node =
(SELECT g2.GroupName AS '@Title', gf.ChildID AS '@ID', gf.ParentID AS '@ParentID',
CASE
WHEN (SELECT COUNT(*) FROM RecursiveGroupFinding AS P2 WHERE P2.ChildID = gf.ParentID) > 0
THEN (SELECT dbo.NodeBuilder2(gf.ChildID))
ELSE NULL
END
FROM RecursiveGroupFinding AS gf
JOIN
GroupList AS g
ON gf.ParentID = g.GroupID
JOIN
GroupList AS g2
ON gf.ChildID = g2.GroupID
WHERE gf.ParentID = @BelongsTo
FOR XML PATH('Group'), TYPE)
RETURN @Node
END
;
GO
-- XML builder, uses function to create child nodes.
WITH GroupParentChild
AS (
SELECT gf.ChildID, g2.GroupName, gf.ParentID
FROM RecursiveGroupFinding AS gf
LEFT OUTER JOIN
GroupList AS g
ON gf.ParentID = g.GroupID
JOIN
GroupList AS g2
ON gf.ChildID = g2.GroupID
)
SELECT
P0.GroupName AS '@Title', P0.ChildID AS '@ID', P0.ParentID AS '@ParentID',
(SELECT dbo.NodeBuilder2(P0.ChildID))
FROM GroupParentChild AS P0
WHERE P0.ParentID IS NULL
FOR XML PATH('Group'), TYPE, ROOT('Root')
I changed some of the code so that a group could have NULL parents, and since that could result in multiple top-parent groups, I put a Root element at the top.
The output should look like:
<Root>
<Group Title="Parent1" ID="1">
<Group Title="Child1" ID="3" ParentID="1">
<Group Title="SubChild1" ID="6" ParentID="3">
<Group Title="Icing" ID="9" ParentID="6" />
</Group>
<Group Title="Subchild2" ID="7" ParentID="3">
<Group Title="Icing" ID="9" ParentID="7" />
</Group>
</Group>
<Group Title="Child2" ID="4" ParentID="1">
<Group Title="Subchild3" ID="8" ParentID="4" />
</Group>
</Group>
<Group Title="Parent2" ID="2">
<Group Title="Child1" ID="3" ParentID="2">
<Group Title="SubChild1" ID="6" ParentID="3">
<Group Title="Icing" ID="9" ParentID="6" />
</Group>
<Group Title="Subchild2" ID="7" ParentID="3">
<Group Title="Icing" ID="9" ParentID="7" />
</Group>
</Group>
<Group Title="Child3" ID="5" ParentID="2" />
</Group>
</Root>