FOR XML AUTO change in 2005?

In 2005, the FOR XML AUTO seems to look into subselects and create nodes
based on those. This is different from 2000. Is this an enhancement or a
bug in '05?
We have a query that joins 4 select statements and returns them in XML.
e.g.
SELECT tree.tree
sr.Enum
sr.Name
cycle.Enum
cycle.Name
cycle.Code
task.TaskID
task.CycleID
task.Recipients
FROM
(select '' as tree) as tree
CROSS JOIN
(select dc.Enum
,r.Name
from dc inner join r on dc.dc_id = r.dc_id) as cycle
CROSS JOIN
(select dc.enum
.... <and so on>
The XML was returning the cycle values as dc and r elements instead of cycle
elements as it did in 2000.
I am just curious if this is getting fixed or if it is the expected behavior
going forward.
To fix this, we now use table variables in place of the embedded selects.
Thanks,
Steve Hughes
Yes, we have done some changes (bug fixes) on how the Auto mode treats
subselects. In SQL Server 2005 it will NOT look into the subselects, while
in 2000 it did. So for SQL Server 2005, derived tables can be used to group
under the same element columns from different tables, but hide the join from
AUTO mode heuristics.
There is an example in the compat level section in BooksOnLine at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm:
USE AdventureWorks
CREATE TABLE Test(id int);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
SELECT * FROM (SELECT a.id AS a,
b.id AS b FROM Test a
JOIN Test b ON a.id=b.id)
Test FOR XML AUTO;
When the compatibility level for AdventureWorks is set to 80, the above
example produces:
<a a="1"><b b="1"/></a>
When the compatibility level for AdventureWorks is set to 90, the preceding
example produces:
<Test a="1" b="1"/>
<Test a="2" b="2"/>
For additional information about other FOR XML changes, please refer to
http://msdn.microsoft.com/library/de...forxml2k5.asp.
HTH
Michael
PS: If you see a different behaviour, please forward me a complete repro...
"Steve Hughes" <SteveHughes@.discussions.microsoft.com> wrote in message
news:7639A6EB-4BDE-4095-ADDD-968B6DB1DEE6@.microsoft.com...
> In 2005, the FOR XML AUTO seems to look into subselects and create nodes
> based on those. This is different from 2000. Is this an enhancement or a
> bug in '05?
> We have a query that joins 4 select statements and returns them in XML.
> e.g.
> SELECT tree.tree
> sr.Enum
> sr.Name
> cycle.Enum
> cycle.Name
> cycle.Code
> task.TaskID
> task.CycleID
> task.Recipients
> FROM
> (select '' as tree) as tree
> CROSS JOIN
> (select dc.Enum
> ,r.Name
> from dc inner join r on dc.dc_id = r.dc_id) as cycle
> CROSS JOIN
> (select dc.enum
> ... <and so on>
> The XML was returning the cycle values as dc and r elements instead of
> cycle
> elements as it did in 2000.
> I am just curious if this is getting fixed or if it is the expected
> behavior
> going forward.
> To fix this, we now use table variables in place of the embedded selects.
> Thanks,
> Steve Hughes
>
>
>
|||This makes sense since we are running in 80 compatibility on SQL 2005 at the
moment. However, in 2000 it worked like it does in 90 compatability. I will
do some further testing to verify, but 80 is not truly running this the way
it did in SQL 2000 in either case.
I will try to follow up again shortly.
Thanks,
Steve
"Michael Rys [MSFT]" wrote:

> Yes, we have done some changes (bug fixes) on how the Auto mode treats
> subselects. In SQL Server 2005 it will NOT look into the subselects, while
> in 2000 it did. So for SQL Server 2005, derived tables can be used to group
> under the same element columns from different tables, but hide the join from
> AUTO mode heuristics.
> There is an example in the compat level section in BooksOnLine at
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm:
> USE AdventureWorks
> CREATE TABLE Test(id int);
> INSERT INTO Test VALUES(1);
> INSERT INTO Test VALUES(2);
> SELECT * FROM (SELECT a.id AS a,
> b.id AS b FROM Test a
> JOIN Test b ON a.id=b.id)
> Test FOR XML AUTO;
> When the compatibility level for AdventureWorks is set to 80, the above
> example produces:
> <a a="1"><b b="1"/></a>
> When the compatibility level for AdventureWorks is set to 90, the preceding
> example produces:
> <Test a="1" b="1"/>
> <Test a="2" b="2"/>
> For additional information about other FOR XML changes, please refer to
> http://msdn.microsoft.com/library/de...forxml2k5.asp.
> HTH
> Michael
> PS: If you see a different behaviour, please forward me a complete repro...
> "Steve Hughes" <SteveHughes@.discussions.microsoft.com> wrote in message
> news:7639A6EB-4BDE-4095-ADDD-968B6DB1DEE6@.microsoft.com...
>
>
|||Ok. I would be interested in seeing the differences.
The problem with 2000 was that it was not necessarily predictable what
naming was chosen.
Best regards
Michael
"Steve Hughes" <SteveHughes@.discussions.microsoft.com> wrote in message
news:1D2E34BB-BC26-4AA2-92C1-7EBDB5478709@.microsoft.com...[vbcol=seagreen]
> This makes sense since we are running in 80 compatibility on SQL 2005 at
> the
> moment. However, in 2000 it worked like it does in 90 compatability. I
> will
> do some further testing to verify, but 80 is not truly running this the
> way
> it did in SQL 2000 in either case.
> I will try to follow up again shortly.
> Thanks,
> Steve
> "Michael Rys [MSFT]" wrote: