Home > Articles > Web Services > XML

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

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.

  • + Share This
  • 🔖 Save To Your Account