Multiple joins in For XML Auto, Elements

Hi,
I'm trying to join a number of tables to produce an xml from a stored
procedure. The trouble is that the results from every new table I join get
produced as a child node to the previous table's node.
Select BG.*, CA.*, NA.*, ET.*
From BG (nolock)
Left Outer Join CA (nolock) On BG.ID = CA.BGID
Left Outer Join ET (nolock) On ET.CAID = CA.ID
Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
Results in the follwing structure
<BG>
<CA>
<NA>
<ET></ET>
</NA>
</CA>
</BG>
Is there a way I can make the xml structure come out as
<BG>
<CA>
<NA></NA>
<ET></ET>
</CA>
</BG>
Regards.
"Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> Hi,
> I'm trying to join a number of tables to produce an xml from a stored
> procedure. The trouble is that the results from every new table I join get
> produced as a child node to the previous table's node.
> Select BG.*, CA.*, NA.*, ET.*
> From BG (nolock)
> Left Outer Join CA (nolock) On BG.ID = CA.BGID
> Left Outer Join ET (nolock) On ET.CAID = CA.ID
> Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
> Results in the follwing structure
> <BG>
> <CA>
> <NA>
> <ET></ET>
> </NA>
> </CA>
> </BG>
> Is there a way I can make the xml structure come out as
> <BG>
> <CA>
> <NA></NA>
> <ET></ET>
> </CA>
> </BG>
> Regards.
It gets even worse than that if NA or ET return multiple rows. I finally
gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the structure
I wanted. I expect you will need to do the same.
|||This is by design (and is explained in the documentation).
The automode looks at the data lineage and not the query string. Thus it
does not know how the data has been joined in. So it assumes a single
nesting hierarchy.
To get sibling trees, you need to use the explicit mode in SQL Server 2000,
or you can use the much simpler nested FOR XML expressions if you can move
to SQL Server 2005.
Best regards
Michael
"Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
> "Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
> news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> It gets even worse than that if NA or ET return multiple rows. I finally
> gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the
> structure I wanted. I expect you will need to do the same.
>
|||thanks Andy / Michael,
will do, till i migrate to 2005
"Michael Rys [MSFT]" wrote:

> This is by design (and is explained in the documentation).
> The automode looks at the data lineage and not the query string. Thus it
> does not know how the data has been joined in. So it assumes a single
> nesting hierarchy.
> To get sibling trees, you need to use the explicit mode in SQL Server 2000,
> or you can use the much simpler nested FOR XML expressions if you can move
> to SQL Server 2005.
> Best regards
> Michael
> "Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
> news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
>
>