Re: FOR XML AUTO change in 2005?
- From: Steve Hughes <SteveHughes@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 22 Feb 2006 13:16:26 -0800
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/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp.
HTH
Michael
PS: If you see a different behaviour, please forward me a complete repro...
"Steve Hughes" <SteveHughes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7639A6EB-4BDE-4095-ADDD-968B6DB1DEE6@xxxxxxxxxxxxxxxx
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
- Follow-Ups:
- Re: FOR XML AUTO change in 2005?
- From: Michael Rys [MSFT]
- Re: FOR XML AUTO change in 2005?
- References:
- Re: FOR XML AUTO change in 2005?
- From: Michael Rys [MSFT]
- Re: FOR XML AUTO change in 2005?
- Prev by Date: Re: Extracting SetOptions in the TSQLCommand section of EVENTDATA
- Next by Date: Re: Problem with inline validation
- Previous by thread: Re: FOR XML AUTO change in 2005?
- Next by thread: Re: FOR XML AUTO change in 2005?
- Index(es):
Relevant Pages
|
