|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Upgraded to SQL 2005, now FOR XML AUTO clause doesn't work2000, 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 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. Show quoteHide quote "Andre Perusse" <andre.perusse@NO.gmail.SPAM.com> wrote in message news:%23Qts$q57FHA.3660@TK2MSFTNGP09.phx.gbl... > 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 > > > > Hi Eugene.
Thanks for the super-fast response and details on how to fix up my queries. Very, very much appreciated. Cheers, Andre Show quoteHide quote "Eugene Kogan [MSFT]" <eko***@online.microsoft.com> wrote in message news:uqn%23ay67FHA.3232@TK2MSFTNGP12.phx.gbl... > 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@NO.gmail.SPAM.com> wrote in message > news:%23Qts$q57FHA.3660@TK2MSFTNGP09.phx.gbl... >> 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 >> >> >> >> > >  
Other interesting topics
Query: Using dynamic XQuery expressions (urgent)
Getting Empty Tags Returned From The Server How are transactions managed for web services Return Value from SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute Confirm methodology or advise how to generate performance reports for eCommerce facility. xml-dml insert element with an attribute containig sql:variable Add perent element Sequential vs. Simultaneous processing dynamic XML using for XML explicit OPENXML won't return rows |
|||||||||||||||||||||||
