Suggestions on Troubleshooting "FOR XML EXPLICIT" Msg 6833 "requires parent tags to be opened first" Error

Question

I have inherited a 1000-line stored procedure which produces XML by using FOR XML EXPLICIT. My problem is that it works most of the time. In some scenarios I get the error:

Parent tag ID 2 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set. Number:6833 Severity:16 State:1

I need ideas on how to troubleshoot this. I need to find out where the nesting is failing. It's probably a case of a parent row not being emitted but a child row is. Worse, the problem only happens on our test system, which is probably missing some of the Production data. The question is how to find this out of thousands of rows?

One wild idea that I'm sure doesn't exist: SQL Server has an algorithm it is using to determine whether the rows are in the correct order. It would be wonderful (if unlikely) if there were a tool that would look at my resultset (without the FOR XML EXPLICIT) and find out where the problem is, then tell me about it.

In the absence of such a tool, I welcome any suggestions on how to debug this. The XML (when it works) is four levels deep!

---

Update: Thanks for all the answers so far. It is looking like this is a question of a badly-edited stored procedure. Large sections were commented out with "/* /" comments - which don't work very well when there are already "/ */" comments in the code... I'll update again when I'm sure of the answer.

Answer 1

One possible way is to actually remove the FOR XML EXPLICIT part and look at the resulting resultset generated by your sql statement. It will give an indication of the nesting that is generating the xml and hopefully direct you to the issue. See the below image which is taken from the MSDN documentation at: http://msdn.microsoft.com/en-us/library/ms189068.aspx.

---

EDIT

It may be worth posting a sample output, but in the example in the image you would get the same error if Order!2!Id for any of the rows with tag=3 was null. This column is effectively the join between parent rows with tag=2 and child rows with tag=3. If your data was like above I think you could effectively find your issue by identifying rows with parent=2 and Order!2!Id is null.

Alternatively it could be ordering. In which case you could somehow build a query that identifies any rows with Parent = 2 occurring before rows with Tag = 2 in the resultset.

---

Edit 2

CREATE TABLE MyTable(
    Tag int,
    Parent int,
    SomeIdentifier int
)   

INSERT INTO MyTable VALUES (2, 1, 1) -- this row defined before parent
INSERT INTO MyTable VALUES (1, null, 1)
INSERT INTO MyTable VALUES (3, 2, 1)
INSERT INTO MyTable VALUES (3, 2, 1)
INSERT INTO MyTable VALUES (1, null, 2)
INSERT INTO MyTable VALUES (2, 1, 2)
INSERT INTO MyTable VALUES (3, 2, 2)
INSERT INTO MyTable VALUES (3, 2, 2)
INSERT INTO MyTable VALUES (1, null, 3)
INSERT INTO MyTable VALUES (3, 2, 3) -- this is orphaned
INSERT INTO MyTable VALUES (3, 2, 3) -- this is orphaned

;WITH myCte AS(
SELECT   Tag
        ,Parent
        ,SomeIdentifier
        ,ROW_NUMBER() OVER (PARTITION BY SomeIdentifier ORDER BY(SELECT 0)) AS RowOrder
FROM    MyTable   
) SELECT c1.Tag
        ,c1.Parent
        ,c1.SomeIdentifier
FROM myCte c1 
LEFT OUTER JOIN myCte c2 ON c2.SomeIdentifier = c1.SomeIdentifier AND c1.Parent = c2.Tag
WHERE c1.Parent IS NOT NULL     --ignore root rows for now
AND   (c1.RowOrder < c2.RowOrder    --out of order rows
        OR    
       c2.Tag IS NULL)      --orphaned rows