XML Explicit Nesting Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

XML Explicit Nesting Problem

Author

Topic

mihnea.radulescu
Starting Member

5 Posts

Posted - 2011-05-16 : 10:24:46

Hi all,

I have this FOR XML AUTO query:

USE AdventureWorks
GO

SELECT Contact.LastName, Contact.FirstName,
	   [Order].SalesOrderID,
	   Detail.UnitPrice, Detail.OrderQty, Detail.LineTotal
FROM Person.Contact AS Contact
	INNER JOIN Sales.SalesOrderHeader AS [Order] ON Contact.ContactID = [Order].ContactID
	INNER JOIN Sales.SalesOrderDetail AS Detail ON [Order].SalesOrderID = Detail.SalesOrderID
ORDER BY Contact.ContactID, [Order].SalesOrderID, Detail.SalesOrderDetailID
FOR XML AUTO, ROOT('Orders')

I would like the following FOR XML EXPLICIT query to provide the same result, but the nesting seems to be flawed:

USE AdventureWorks
GO

SELECT 1 AS Tag,
	   NULL AS Parent,
	   Contact.LastName AS 'Contact!1!LastName',
	   Contact.FirstName AS 'Contact!1!FirstName',
	   Contact.ContactID AS 'Contact!1!ContactID!HIDE',
	   [Order].SalesOrderID AS 'Order!2!SalesOrderID',
	   Detail.SalesOrderDetailID AS 'Detail!3!SalesOrderDetailID!HIDE',
	   NULL AS 'Detail!3!UnitPrice',
	   NULL AS 'Detail!3!OrderQty',
	   NULL AS 'Detail!3!LineTotal'
FROM Person.Contact AS Contact
INNER JOIN Sales.SalesOrderHeader AS [Order] ON Contact.ContactID = [Order].ContactID
INNER JOIN Sales.SalesOrderDetail AS Detail ON [Order].SalesOrderID = Detail.SalesOrderID
UNION ALL
SELECT 2 AS Tag,
	   1 AS Parent,
	   NULL AS 'Contact!1!LastName',
	   NULL AS 'Contact!1!FirstName',
	   Contact.ContactID AS 'Contact!1!ContactID!HIDE',
	   [Order].SalesOrderID AS 'Order!2!SalesOrderID',
	   Detail.SalesOrderDetailID AS 'Detail!3!SalesOrderDetailID!HIDE',
	   NULL AS 'Detail!3!UnitPrice',
	   NULL AS 'Detail!3!OrderQty',
	   NULL AS 'Detail!3!LineTotal'
FROM Person.Contact AS Contact
INNER JOIN Sales.SalesOrderHeader AS [Order] ON Contact.ContactID = [Order].ContactID
INNER JOIN Sales.SalesOrderDetail AS Detail ON [Order].SalesOrderID = Detail.SalesOrderID
UNION ALL
SELECT 3 AS Tag,
	   2 AS Parent,
	   NULL AS 'Contact!1!LastName',
	   NULL AS 'Contact!1!FirstName',
	   Contact.ContactID AS 'Contact!1!ContactID!HIDE',
	   [Order].SalesOrderID AS 'Order!2!SalesOrderID',
	   Detail.SalesOrderDetailID AS 'Detail!3!SalesOrderDetailID!HIDE',
	   Detail.UnitPrice AS 'Detail!3!UnitPrice',
	   Detail.OrderQty AS 'Detail!3!OrderQty',
	   Detail.LineTotal AS 'Detail!3!LineTotal'
FROM Person.Contact AS Contact
INNER JOIN Sales.SalesOrderHeader AS [Order] ON Contact.ContactID = [Order].ContactID
INNER JOIN Sales.SalesOrderDetail AS Detail ON [Order].SalesOrderID = Detail.SalesOrderID
ORDER BY 'Contact!1!ContactID!HIDE', 'Order!2!SalesOrderID', 'Detail!3!SalesOrderDetailID!HIDE'
FOR XML EXPLICIT, ROOT('Orders')

I am wondering what is wrong with the second query. Any hint would be helpful.

Best regards,
Mihnea

mihnea.radulescu
Starting Member

5 Posts

Posted - 2011-05-16 : 10:42:44

It seems the FOR XML PATH retrieves the same result (nested incorrectly) as FOR XML EXPLICIT:

USE AdventureWorks
GO

SELECT Contact.LastName AS '@LastName',
	   Contact.FirstName AS '@FirstName',
	   [Order].SalesOrderID AS 'Order/@SalesOrderID',
	   Detail.UnitPrice AS 'Order/Detail/@UnitPrice',
	   Detail.OrderQty AS 'Order/Detail/@OrderQty',
	   Detail.LineTotal AS 'Order/Detail/@LineTotal'
FROM Person.Contact AS Contact
	INNER JOIN Sales.SalesOrderHeader AS [Order] ON Contact.ContactID = [Order].ContactID
	INNER JOIN Sales.SalesOrderDetail AS Detail ON [Order].SalesOrderID = Detail.SalesOrderID
ORDER BY Contact.ContactID, [Order].SalesOrderID, Detail.SalesOrderDetailID
FOR XML PATH('Contact'), ROOT('Orders')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 11:47:14

For the XML EXPLICIT, test this by removing the FOR XML clause. The result of the select should be ordered correctly. I suspect that you would need to change your query to something like this:


SELECT 1 AS Tag,
      NULL AS Parent,
      Contact.LastName AS 'Contact!1!LastName',
      Contact.FirstName AS 'Contact!1!FirstName',
      Contact.ContactID AS 'Contact!1!ContactID!HIDE',
      NULL  AS 'Order!2!SalesOrderID',
      NULL  AS 'Detail!3!SalesOrderDetailID!HIDE',
      NULL AS 'Detail!3!UnitPrice',
      NULL AS 'Detail!3!OrderQty',
      NULL AS 'Detail!3!LineTotal'
FROM Person.Contact AS Contact
UNION ALL
SELECT 2 AS Tag,
      1 AS Parent,
      Contact.LastName AS 'Contact!1!LastName',
      Contact.FirstName AS 'Contact!1!FirstName',
      Contact.ContactID AS 'Contact!1!ContactID!HIDE',
      [Order].SalesOrderID AS 'Order!2!SalesOrderID',
      NULL  AS 'Detail!3!SalesOrderDetailID!HIDE',
      NULL AS 'Detail!3!UnitPrice',
      NULL AS 'Detail!3!OrderQty',
      NULL AS 'Detail!3!LineTotal'
FROM Person.Contact AS Contact
INNER JOIN Sales.SalesOrderHeader AS [Order] ON Contact.ContactID = [Order].ContactID
....

XML EXPLICIT is deprecated, so Microsoft recommendation is that you use FOR XML PATH in new development.

In your FOR XML PATH, you will need to nest the queries like this:


SELECT
   Contact.LastName AS '@LastName',
   Contact.FirstName AS '@FirstName',
   (SELECT 
   
      [Order].SalesOrderID AS '@SalesOrderID',
      (
         SELECT
            Detail.UnitPrice AS '@UnitPrice',
            Detail.OrderQty AS '@OrderQty',
            Detail.LineTotal AS '@LineTotal'
          FROM Sales.SalesOrderDetail AS Detail
          WHERE [Order].SalesOrderID = Detail.SalesOrderID
          ORDER BY Detail.SalesOrderDetailID
          FOR XML PATH('Detail'), TYPE 
      )
   FROM Sales.SalesOrderHeader AS [Order]
   WHERE Contact.ContactID = [Order].ContactID
   ORDER BY [Order].SalesOrderID
   FOR XML PATH('Order'),TYPE 
   )
FROM
   Person.Contact AS Contact
ORDER BY
   Contact.ContactID
   FOR XML PATH('Contact'),
   ROOT('Orders')

I may not have the exact ordering and nesting done correctly here, but I hope you can see that the inner elements are placed there by nesting a select in the outer select.

If you choose to use XML PATH and run into difficulties, post the code, and I can try to help. As for XML EXPLICIT, it is too complex for my head to hold.

mihnea.radulescu
Starting Member

5 Posts

Posted - 2011-05-16 : 13:50:52

Thanks a lot sunitabeck for the elegant FOR XML PATH solution!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 14:16:39

You are quite welcome!

I would like to take all the credit for the elegance, but those pesky people at Microsoft had already figured out the elegant ways; all I was doing was copying it from them: http://msdn.microsoft.com/en-us/library/ms189885.aspx and the examples link that is on that page.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources