nested XML output with several JOIN statements

First, thanks to the guys for helping me earlier just to get my XML output
into element form. I'm new to XML, so I appreciate all the help and hope I'm
learning.
My next question has to do with nesting the data that comes out as a result
of several JOIN's
Here is my code:
DECLARE @.t TABLE (id INT)
INSERT INTO @.t VALUES (1)
SELECT
Root.id,
[Order].ID,
[Agent].TextID,
[Buyer].TextID,
[Broker].TextID
FROM
@.t AS Root
JOIN psOrder [Order] ON Root.id = 1
LEFT JOIN Contact [Agent] ON [Order].agent_id = Agent.ID
LEFT JOIN Contact [Buyer] ON [Order].buyer_id = Buyer.ID
LEFT JOIN Contact [Broker] ON [Order].broker_id = Broker.ID
WHERE [Order].ID = 12345
FOR XML AUTO, ELEMENTS
The "Order" table joins to the "Agent", "Buyer", and "Broker" tables.
However, the output I'm getting is nesting the data.
The output I get (incorrectly) is like this:
<Order>
<Agent>
<Buyer>
<Broker>
</Broker>
</Buyer>
</Agent>
</Order>
This is not correct. The Agent, Buyer, and Broker are all children of the
Order, and should not be nested within each other.
The desired output is like this:
<Order>
<Agent>
</Agent>
<Buyer>
</Buyer>
<Broker>
</Broker>
</Order>
I'd would very appreciate some help with this. Perhaps my SQL is not written
properly so that it comes out as desired.
ScottFor each of your LEFT JOIN's, towards the end, add "AND Root.id = 1" to get
the correct nesting. For example:
=====
DECLARE @.t TABLE (id INT)
INSERT INTO @.t VALUES (1)
SELECT
Root.id,
authors.au_id, authors.au_lname, authors.au_fname,
titles.title_id, titles.title
FROM
@.t AS Root
JOIN authors ON Root.id = 1
JOIN titleauthor ON authors.au_id = titleauthor.au_id AND Root.id = 1
JOIN titles ON titleauthor.title_id = titles.title_id AND Root.id = 1
FOR XML AUTO, ELEMENTS
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:umPgdiA8FHA.3388@.TK2MSFTNGP11.phx.gbl...
> First, thanks to the guys for helping me earlier just to get my XML output
> into element form. I'm new to XML, so I appreciate all the help and hope
> I'm
> learning.
> My next question has to do with nesting the data that comes out as a
> result
> of several JOIN's
> Here is my code:
> DECLARE @.t TABLE (id INT)
> INSERT INTO @.t VALUES (1)
> SELECT
> Root.id,
> [Order].ID,
> [Agent].TextID,
> [Buyer].TextID,
> [Broker].TextID
> FROM
> @.t AS Root
> JOIN psOrder [Order] ON Root.id = 1
> LEFT JOIN Contact [Agent] ON [Order].agent_id = Agent.ID
> LEFT JOIN Contact [Buyer] ON [Order].buyer_id = Buyer.ID
> LEFT JOIN Contact [Broker] ON [Order].broker_id = Broker.ID
> WHERE [Order].ID = 12345
> FOR XML AUTO, ELEMENTS
> The "Order" table joins to the "Agent", "Buyer", and "Broker" tables.
> However, the output I'm getting is nesting the data.
> The output I get (incorrectly) is like this:
> <Order>
> <Agent>
> <Buyer>
> <Broker>
> </Broker>
> </Buyer>
> </Agent>
> </Order>
> This is not correct. The Agent, Buyer, and Broker are all children of the
> Order, and should not be nested within each other.
> The desired output is like this:
> <Order>
> <Agent>
> </Agent>
> <Buyer>
> </Buyer>
> <Broker>
> </Broker>
> </Order>
> I'd would very appreciate some help with this. Perhaps my SQL is not
> written
> properly so that it comes out as desired.
> Scott
>|||Thanks for the reply SriSamp. I've added the " AND Root.id = 1" at the end
of each LEFT JOIN, but I'm still getting the nesting problem.
The data is still coming out nested like this (incorrectly):
<Order>
<Agent>
<Buyer>
<Broker>
</Broker>
</Buyer>
</Agent>
</Order>
Just to confirm, I do not want the child table data to nest one within each
other.
The desired output is this:
<Order>
<Agent>
</Agent>
<Buyer>
</Buyer>
<Broker>
</Broker>
</Order>
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:OWdNAvA8FHA.1000@.tk2msftngp13.phx.gbl...
> For each of your LEFT JOIN's, towards the end, add "AND Root.id = 1" to
get
> the correct nesting. For example:
> =====
> DECLARE @.t TABLE (id INT)
> INSERT INTO @.t VALUES (1)
> SELECT
> Root.id,
> authors.au_id, authors.au_lname, authors.au_fname,
> titles.title_id, titles.title
> FROM
> @.t AS Root
> JOIN authors ON Root.id = 1
> JOIN titleauthor ON authors.au_id = titleauthor.au_id AND Root.id = 1
> JOIN titles ON titleauthor.title_id = titles.title_id AND Root.id = 1
> FOR XML AUTO, ELEMENTS
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:umPgdiA8FHA.3388@.TK2MSFTNGP11.phx.gbl...
output
the
>