- The EXPLICIT Mode of FOR XML
- More than One Child Tag
- Choosing the Columns for the ORDER BY Clause
- Parent Tag Not Open Error
- Summary
Parent Tag Not Open Error
One of the dreaded errors when working with FOR XML EXPLICIT is error number 6833. The cryptic nature of the error message doesn't really help either:
"Parent tag ID N is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set."
The truth is that ordering of the result set might or might not be the reason. Sometimes, your ORDER BY clause looks fine, but you still get the error. In such cases, ensure that all the joining columns appear in each SELECT statement. The following example returns the error 6833 because the second SELECT statement does not contain CustomerID, which is used to join Orders and Customers tables:
SELECT 1 AS TAG, NULL AS PARENT, Customers.CustomerID AS [Customers!1!CustomerID], Customers.CompanyName AS [Customers!1!CompanyName], NULL AS [Orders!2!OrderID], NULL AS [Orders!2!OrderDate], NULL AS [Orders!2!ShipCity], NULL AS [OrderDetails!3!ProductID], NULL AS [OrderDetails!3!Quantity], NULL AS [Products!4!ProductName] FROM Customers INNER JOIN Orders ON Customers.CustomerID = ORders.CustomerID INNER JOIN [Order Details] OD ON od.OrderID = Orders.OrderID INNER JOIN Products ON Products.ProductID = OD.ProductID WHERE Products.ProductID = 9 UNION ALL SELECT 2 AS TAG, 1 AS PARENT, NULL AS [Customers!1!CustomerID], NULL AS [Customers!1!CompanyName], Orders.OrderID AS [Orders!2!OrderID], Orders.OrderDate AS [Orders!2!OrderDate], Orders.ShipCity AS [Orders!2!ShipCity], NULL AS [OrderDetails!3!ProductID], NULL AS [OrderDetails!3!Quantity], NULL AS [Products!4!ProductName] FROM Customers INNER JOIN Orders ON customers.customerID = Orders.CustomerID INNER JOIN [Order Details] OD ON od.OrderID = Orders.OrderID INNER JOIN Products ON Products.ProductID = OD.ProductID WHERE Products.ProductID = 9 UNION ALL SELECT 3 AS TAG, 2 AS PARENT, Customers.CustomerID AS [Customers!1!CustomerID], NULL AS [Customers!1!CompanyName], Orders.OrderID AS [Orders!2!OrderID], NULL AS [Orders!2!OrderDate], NULL AS [Orders!2!ShipCity], OD.ProductID AS [OrderDetails!3!ProductID], OD.Quantity AS [OrderDetails!3!Quantity], NULL AS [Products!4!ProductName] FROM Customers INNER JOIN Orders ON customers.customerID = Orders.CustomerID INNER JOIN [Order Details] OD ON OD.OrderID = Orders.OrderID INNER JOIN Products ON Products.ProductID = OD.ProductID WHERE Products.ProductID = 9 ORDER BY [Customers!1!CustomerID], [Orders!2!OrderID] FOR XML EXPLICIT
To fix the error, all I have to do is replace the NULL with Customers.CustomerID in the underlined section of code. Then, the query will run as expected. Therefore, watch out for error 6833 and ensure all your joining columns appear in all SELECT statements.