When the database compatibility level is set to 90 or later, FOR XML queries that execute in AUTO mode return references ...

When the database compatibility level is set to 90 or later, FOR XML queries that execute in AUTO mode return references to derived table aliases. When the compatibility level is set to 80, FOR XML AUTO queries return references to the base tables that define a derived table. For example, the following query, which includes a derived table, produces different results under compatibility levels 80, 90, or later:

SELECT * FROM 
   (SELECT a.id AS a, b.id AS b 
    FROM Test a JOIN Test b ON a.id=b.id) AS DerivedTest FOR XML AUTO;

Under compatibility level 80, the query returns the following results. The results reference the base table aliases a and b of the derived table instead of the derived table alias.

        

Under compatibility level 90 or later, the query returns references to the derived table alias DerivedTest instead of to the derived table's base tables.