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.

Electrum

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

Mede

On a review of your question I see you may have already considered this, but perhaps you can group the result set on an identifier that uniquely identifies each xml doc and the Parent column and see where you have any entries with a Parent count less than 2 (excluding null for the root) – Mede

@cpedros: yes, I did try removing the "for xml explicit". Could you give an example of your grouping suggestion? – Electrum

@John apologies what I had in mind for grouping won't work. As you've probably found out the error can be as a consequence of a number of reasons. I've added some thoughts to the edit ... – Mede

@cpedros: if you can give an example of a query I could use to find out of order tags, then I'd mark this as the answer. Even if not a specific query for the tag order, just a link to how to do queries where sequence of rows matters (i.e., compare a row to a previous row) – Electrum

@John You could try the edit I've made. I appreciate this is largely dependent on some implied ordering for which I don't think any guarantee can be made, but for the sample data set I used this worked. – Mede

@cped: I see i forgot to tag the question with [sql-server-2000]. Still, I;ll try your example on a 2995 machine, and include it as a reason to upgrade. – Electrum

best troubleshooting step - remove remove the FOR XML EXPLICIT – Fleshpots

Sometimes it's as simple with replacing a NULL as a valid sortable value, e.g. a numerical or string default (''). – Cymbal

Answer 2

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.

Propinquity

thanks. The problem is simply that the SELECT query is failing because, if it were to construct the XML according to the rowset, the XML would then not be well-formed. Unfortunately, it doesn't say which of the 5,000 rows in the rowset would cause this problem, and it won't proceed and produce non well-formed XML! – Electrum

Answer 3

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 :-)

Swivet

Answer 4

I found the answer for one of these issues, and wanted to share some lessons I learned.

I took the guts of the stored procedure, and changed it so that it inserted the resultset into a table variable. Lesson 1: make sure you get the column types correct in the table variable - I spent hours chasing a problem caused by inadvertently changing the column type from varchar to int, which caused a change in sort order, which moved the problem.

Once I fixed my table variable, I was able to do some useful queries, like:

SELECT TOP n *
FROM @result
ORDER BY <same order as original query>
FOR XML EXPLICIT

I thought I'd have to do a "binary search" to determine which row had the problem. As it turns out, the problem was in the first few rows.

The level 2 data was formed by a query which included an inner join to a lookup table. This caused the entire row to be omitted whenever the lookup column didn't map. This did not prevent the corresponding level 3 and 4 rows from being emitted, so this caused the error.

Using LEFT JOIN for the lookups solved the problem.

Electrum