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

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

When using FOR XML, the order of the result set must have the parent xml nodes before their children (In general, XML files should not rely on being ordered; this should be performed using an XSL transformation)

Of interest: The Art of XSD (free ebook)

You probably know this already: If you have the XSD, you can use a tool to validate the XML against the XSD (or write approx. 10 lines of C# to do it):

How To Validate an XML Document by Using DTD, XDR, or XSD in Visual C# .NET

If you have an example of the well formed XML, you can generate a XSD using XSD.exe.

I'd dump the rowset (without the FOR XML clause) into a temp table. You should then be able to perform searches within this table for orphans.

If you can't find any orphans, it would tend to indicate that there's an issue with your ordering (the parent is in the rowset, but appears after the children). But at least we'll have halved the search space for the issue :-)