FOR XML query with more than 2 levels of data

I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.

In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.

I'm using the Bush family in this example, these are the relationships:
- George SR
-- George JR
-- Jenna
-- Barbara
-- Jeb
-- Jeb JR
-- Noelle

These statements will create and populate the tables for the example with the above relationships:

SET NOCOUNT ON
DROP TABLE KK_Kids, DD_Dads, GG_Grandpas
CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20))
CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20))
CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))

ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Dad_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT PK_KK PRIMARY KEY (KK_Kid_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT FK_KK FOREIGN KEY (KK_Dad_Key) REFERENCES DD_Dads (DD_Dad_Key)

INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR')
INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR')
INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' )
INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' )
INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' )
INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )

So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads.
I am getting this, which is not what I want:

- George SR
-- George JR
-- Jeb
-- Jenna
-- Barbara
-- Jeb JR
-- Noelle

SELECT 1 as Tag,
NULL as Parent,
GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName],
GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id],
NULL as [DD_Dads!2!DD_DadName],
NULL as [DD_Dads!2!DD_Dad_Key!id],
NULL as [DD_Dads!2!DD_Grandpa_Key!idref],
NULL as [KK_Kids!3!KK_KidName],
NULL as [KK_Kids!3!KK_Dad_Key!idref]
FROM GG_Grandpas
UNION ALL
SELECT 2 ,
1 ,
NULL ,
GG_Grandpa_Key ,
DD_DadName ,
DD_Dad_Key ,
DD_Grandpa_Key ,
NULL ,
NULL
FROM GG_Grandpas, DD_Dads
WHERE GG_Grandpa_Key = DD_Grandpa_Key
UNION ALL
SELECT 3 ,
2 ,
NULL ,
GG_Grandpa_Key ,
NULL ,
DD_Dad_Key ,
NULL ,
KK_KidName ,
KK_Dad_Key
FROM GG_Grandpas, DD_Dads , KK_Kids
WHERE GG_Grandpa_Key = DD_Grandpa_Key
AND DD_Dad_Key = KK_Dad_Key

FOR XML EXPLICIT

I've tried it all different ways, but no luck so far.
Any ideas?I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.

Check this out..

SELECT dbo.GG_Grandpas.GG_GrandpaName, dbo.DD_Dads.DD_DadName, dbo.KK_Kids.KK_KidName
FROM dbo.DD_Dads

INNER JOIN dbo.GG_Grandpas
ON dbo.DD_Dads.DD_Grandpa_Key = dbo.GG_Grandpas.GG_Grandpa_Key

INNER JOIN dbo.KK_Kids
ON dbo.DD_Dads.DD_Dad_Key = dbo.KK_Kids.KK_Dad_Key

GROUP BY dbo.GG_Grandpas.GG_GrandpaName, dbo.DD_Dads.DD_DadName, dbo.KK_Kids.KK_KidName

for xml auto

result in xml

<dbo.GG_Grandpas GG_GrandpaName="GEORGE SR">
<dbo.DD_Dads DD_DadName="GEORGE JR">
<dbo.KK_Kids KK_KidName="Barbara" />
<dbo.KK_Kids KK_KidName="Jenna" />
</dbo.DD_Dads>
<dbo.DD_Dads DD_DadName="JEB">
<dbo.KK_Kids KK_KidName="Jeb Junior" />
<dbo.KK_Kids KK_KidName="Noelle" />
</dbo.DD_Dads>
</dbo.GG_Grandpas>

if you need those ids just include those ...|||You can't do a GROUP BY with a FOR XML query, at least not in the version I'm running. I get this message:
GROUP BY and aggregate functions are currently not supported with FOR XML AUTO.

Turns out a simple query does work for my example though:

SELECT GG_GrandpaName, DD_DadName, KK_KidName
FROM GG_Grandpas
LEFT OUTER JOIN DD_Dads ON DD_Grandpa_Key = GG_Grandpa_Key
LEFT OUTER JOIN KK_Kids ON DD_Dads.DD_Dad_Key = KK_Kids.KK_Dad_Key
for xml auto , elements

I think I simplified it too much for my example though, because it's still not working for my real world case.|||I believe I have it now. The trick is in the orderby clause. You have to order the results such that the children fall right after their parents in the result table or else it won't get the relationships correct.
I added another child to my previous example so that there is a separate Sons and Daughters table to fit my realworld problem better. This example might be easier to follow than the one in BOL, so I thought I'd post it.

Here is the code to setup the example:

SET NOCOUNT ON
DROP TABLE SS_Sons, DD_Daughters, FF_Fathers, GG_Grandpas

CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_Name varchar(20))
CREATE TABLE FF_Fathers ( FF_Father_Key varchar(20) NOT NULL, FF_Grandpa_Key varchar(20) NOT NULL, FF_Name varchar(20))
CREATE TABLE SS_Sons ( SS_Son_Key varchar(20) NOT NULL, SS_Father_Key varchar(20) NOT NULL, SS_Name varchar(20))
CREATE TABLE DD_Daughters ( DD_Daughter_Key varchar(20) NOT NULL, DD_Father_Key varchar(20) NOT NULL, DD_Name varchar(20))

ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE FF_Fathers ADD CONSTRAINT PK_FF PRIMARY KEY (FF_Father_Key)
ALTER TABLE SS_Sons ADD CONSTRAINT PK_SS PRIMARY KEY (SS_Son_Key)
ALTER TABLE DD_Daughters ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Daughter_Key)

ALTER TABLE FF_Fathers ADD CONSTRAINT FK_FF FOREIGN KEY (FF_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE SS_Sons ADD CONSTRAINT FK_SS FOREIGN KEY (SS_Father_Key) REFERENCES FF_Fathers (FF_Father_Key)
ALTER TABLE DD_Daughters ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Father_Key) REFERENCES FF_Fathers (FF_Father_Key)

INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE H')
INSERT INTO FF_Fathers VALUES ('FF_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE W')
INSERT INTO FF_Fathers VALUES ('FF_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO SS_Sons VALUES ( 'SS_JebJR_Key', 'FF_JEB_KEY', 'Jeb Junior' )
INSERT INTO DD_Daughters VALUES ( 'DD_Jenna_Key', 'FF_GEORGEJR_KEY', 'Jenna' )
INSERT INTO DD_Daughters VALUES ( 'DD_Barbara_Key', 'FF_GEORGEJR_KEY', 'Barbara' )
INSERT INTO DD_Daughters VALUES ( 'DD_Noelle_Key', 'FF_JEB_KEY', 'Noelle' )

and here is the select statement:

SELECT 1 AS Tag,
NULL as Parent,
GG_Name as [GrandPas!1!GrandpaName!element],
NULL as [Fathers!2!FatherName!element],
NULL as [Sons!3!SonName!element],
NULL as [Daughters!4!DaughterName!element]
FROM GG_Grandpas
UNION ALL
SELECT 2 AS Tag,
1 as Parent,
GG_Name ,
FF_Name ,
NULL ,
NULL
FROM GG_Grandpas
LEFT OUTER JOIN FF_Fathers ON ( FF_Grandpa_Key = GG_Grandpa_Key )
UNION ALL
SELECT 3 AS Tag,
2 as Parent,
GG_Name ,
FF_Name ,
SS_Name ,
NULL
FROM GG_Grandpas
LEFT OUTER JOIN FF_Fathers ON ( FF_Grandpa_Key = GG_Grandpa_Key )
LEFT OUTER JOIN SS_Sons ON (SS_Father_Key = FF_Father_Key)
UNION ALL
SELECT 4 AS Tag,
2 as Parent,
GG_Name ,
FF_Name ,
NULL ,
DD_Name
FROM GG_Grandpas
LEFT OUTER JOIN FF_Fathers ON ( FF_Grandpa_Key = GG_Grandpa_Key )
LEFT OUTER JOIN DD_Daughters ON (DD_Father_Key = FF_Father_Key)
ORDER BY [GrandPas!1!GrandpaName!element], [Fathers!2!FatherName!element], [Sons!3!SonName!element], [Daughters!4!DaughterName!element]
FOR XML EXPLICIT|||You can't do a GROUP BY with a FOR XML query, at least not in the version I'm running. I get this message:
GROUP BY and aggregate functions are currently not supported with FOR XML AUTO.

Which version are you running? I have no problem in group by clause,Mine is SQL SERVER 2005 EXPRESS edition.I just gave you the result in xml also...so there is no error in that...|||I'm running 2000, not 2005:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)