Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: 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:
    Code:
    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

    Code:
    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?

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by craigmc
    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 ...
    Last edited by rudra; 03-31-06 at 09:49.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    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:
    Code:
    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.

  4. #4
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    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:
    Code:
    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:
    Code:
    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

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by craigmc
    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....
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •