I have a query that is returning multiple XML records instead of one
record with multiple children when a joined table has more than one
record. Here is a simplified version of the query:

SELECT Person.PersonID AS "Traveler/PersonID",
Profile.ProfileID AS "Traveler/Profile/ProfileID",
EmailAddress.EmailAddressID AS
"Traveler/Profile/EmailAddess/EmailAddressID"
FROM Person...

Hello tempdba@gmail.com,

Consider writing this as something like (untested):

Yes, that makes sense, but why is it necessary? Can't the query engine
read the relationships and render the xml correctly? I don't see that a
subquery is required for that. It should just work.

Kent Tegels wrote:

Think about how a normal (that is, non-shaped query) works. You're asking
the engine to optimaly select a rectangular resultset. But what you really
want is nested data.

In SQL 2000, the XML serialization of the results was done as a step after
the query was completed but before it pushed down into the TDS for output.
In SQL 2005, the serialization occurs as part of the resultset generation
as the output is an instance of the XML type (typically). SQL 2005 is able
to insert XML instances into another XML instance, so this is how you do it.

Helpful?

Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

Here's why I'm having a problem: The bleeding edge of SQL Server is
receiveing and returning very complex XML data. Complex XML data like -

CREATE [dbo].[usp_xml_Profile_GetByPersonID]
@PersonID uniqueidentifier
AS

/*
EXEC usp_xml_Profile_GetByPersonID @PersonID =
'82F81341-C736-DB11-BFF0-000FFE41B09F'
*/
SELECT
OrganizationAddress.AddressID AS
"Traveler/Organization/Address/AddressID",
OrganizationAddress...

Hello tempdba@gmail.com,

I think this example is horrible either way. You either have a single huge
query like this that's all-but-impossible to maintain OR you have a *potentially*
less performant query based on subqueries. Run this query in your browser
with the actual query plan turned on.:

This is close, and based on what you are doing, I think the closest you can
get without incorporating subqueries.

SELECT Traveler.PersonID, Profile.ProfileID, EmailAddress.EmailAddressID
FROM EmailAddress AS EmailAddress
RIGHT OUTER JOIN Profile_EmailAddress ON
Profile_EmailAddress.EmailAddressID = EmailAddress.EmailAddressID
RIGHT OUTER JOIN Profile ON Profile.ProfileID =
Profile_EmailAddress.ProfileID
INNER JOIN Person_Profile ON Person_Profile.ProfileID =
Profile.ProfileID
INNER JOIN Person AS Traveler ON Traveler.PersonID =
Person_Profile.PersonID
WHERE Traveler.PersonID = '82F81341-C736-DB11-BFF0-000FFE41B09F'
FOR XML AUTO, ROOT('TravelerProfiles'), ELEMENTS

RESULTS IN...



82F81341-C736-DB11-BFF0-000FFE41B09F

AEDBE5B7-8737-DB11-BFF0-000FFE41B09F
...

So, I guess what everyone here is saying, and what MS Support is about
to say, is that XML PATH does not work and that it is impossible to
return complex XML in SQL Server without using XML EXPLICIT, just like
SQL 2000. Remember, this was an extrememly simple, stripped down,
example. An XML document in the real world is much more complicated.

Peter W. DeBetta wrote:

XML PATH works great, and I have created a number of solutions using it,
that in some cases happen to use subqueries.

However, it is not a be-all-end-all solution. You have a situation that is
difficult to implement, but that doesn't mean FOR XML PATH doesn't work. FOR
XML PATH is not a transformation mechanism, and so it cannot take the
multiple rows and makes them appear as one. In other words, if you return 3
rows, you get three main nodes(unless you specify a root, then you get one
main and 3 child nodes). You can return what you want, using subqueries, but
because of the complexity of your query, you choose not to. Either way, the
T-SQL you are writing is complex.

By the way, for your initial example, the FOR XML PATH solution (using
subqueries) is as follows: