Re: Upgraded to SQL 2005, now FOR XML AUTO clause doesn't work

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Andre,

Thank you for providing the steps to reproduce the issue.

Short answers.
By Design? Yes, your query relied on incorrect and undocumented
column-to-table association in UNION ALL in SQL Server 2000.
How to get the formatting back? Rewrite your query (see below) or wait for
SQL Server 2005 SP1 where you can expect the behavior to be restored but
only under SQL Server 2000 compatibility level.

Details.
FOR XML AUTO heuristics on grouping XML tags relies on column associations
with their source tables. The result of UNION [ALL] is supposed to loose
column-to-table association while the resulting column names are taken from
the first leg of the UNION. In SQL Server 2000 column-to-table associations
are also derived from the first leg of the UNION which is incorrect. This
behavior is fixed in SQL Server 2005. However, there is a plan to restore
SQL Server 2000 behavior in SQL Server 2005 Service Pack 1 under 80
compatibility level (SQL Server 2000 compatibility level), and also to
document it in SQL Server 2005 Upgrade Advisor.

There are multiple ways to rewrite the query to get the desired formatting
in SQL Server 2005 based on the new sub-query FOR XML support and on
improved derived table support in FOR XML AUTO. Here's an example of a
cleaner way to associate columns with XML tags using SQL Server 2005 derived
table support for FOR XML AUTO:

SELECT
cat.CategoryID,
cat.CategoryName,
prod.ProductID,
prod.ProductName
FROM
(
SELECT
cat.CategoryID,
cat.CategoryName,
prod.ProductID,
prod.ProductName
FROM
dbo.Categories AS cat
INNER JOIN dbo.Products AS prod ON cat.CategoryID = prod.CategoryID

UNION ALL

SELECT
reg.RegionID,
reg.RegionDescription,
ter.TerritoryID,
ter.TerritoryDescription
FROM
dbo.Region AS reg
INNER JOIN dbo.Territories AS ter ON reg.RegionID = ter.RegionID
) uall
CROSS APPLY
(SELECT uall.CategoryID, uall.CategoryName) cat
CROSS APPLY
(SELECT uall.ProductID, uall.ProductName) prod
ORDER BY cat.CategoryID
FOR XML AUTO

And another example based on sub-query FOR XML:

SELECT
cat.CategoryID,
cat.CategoryName,
(
SELECT
prod.ProductID,
prod.ProductName
FROM
(
SELECT
prod.ProductID,
prod.ProductName,
prod.CategoryID
FROM dbo.Products AS prod

UNION ALL

SELECT
ter.TerritoryID,
ter.TerritoryDescription,
ter.RegionID
FROM dbo.Territories AS ter
) prod
WHERE cat.CategoryID = prod.CategoryID
FOR XML RAW('prod'), TYPE
)
FROM
(
SELECT
cat.CategoryID,
cat.CategoryName
FROM
dbo.Categories AS cat

UNION ALL

SELECT
reg.RegionID,
reg.RegionDescription
FROM
dbo.Region AS reg
) cat
ORDER BY cat.CategoryID
FOR XML RAW('cat')

Andre, these queries should be equivalent to yours. Note that this syntax
only works in SQL Server 2005.

Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.



"Andre Perusse" <andre.perusse@xxxxxxxxxxxxxxxxx> wrote in message
news:%23Qts$q57FHA.3660@xxxxxxxxxxxxxxxxxxxxxxx
> The easiest way to illustrate my problem is to run the following SQL on
> SQL 2000, then on SQL 2005 to see the difference. Don't try to make sense
> of what I'm doing with the data in this particular illustration because it
> doesn't make sense. :-) However, it illustrates that when you perform a
> FOR XML AUTO clause on SQL 2005 where you're using a UNION ALL, that the
> returned XML is not what you expect.
>
> When this is run on SQL 2000, a hierarchical XML structure is returned
> with <prod> elements existing under <cat> elements. However, in SQL 2005,
> the <prod> attributes are embedded within the <cat> elements (remove the
> UNION ALL and the second SELECT statement and SQL 2005 WILL format it
> hierarchically - put the UNION ALL back in and you're screwed).
>
> Can anyone tell me if this behaviour is by design, and if so, how do I get
> back my hierarchical XML when using this method?
>
> (Note: I tried using the same table alias names in both SELECT
> statements - this did not help)
>
>
> Many thanks,
> Andre
>
>
>
> USE Northwind
> GO
>
> SELECT
> cat.CategoryID,
> cat.CategoryName,
> prod.ProductID,
> prod.ProductName
>
> FROM
> dbo.Categories AS cat
> INNER JOIN dbo.Products AS prod ON cat.CategoryID = prod.CategoryID
>
> UNION ALL
>
> SELECT
> reg.RegionID,
> reg.RegionDescription,
> ter.TerritoryID,
> ter.TerritoryDescription
>
> FROM
> dbo.Region AS reg
> INNER JOIN dbo.Territories AS ter ON reg.RegionID = ter.RegionID
>
> ORDER BY cat.CategoryID
>
> FOR XML AUTO
> GO
>
>
>
>


.

Relevant Pages