ORDER BY Clause in FOR XML EXPLICIT

Question

I have a rather large query (25 tags across a dozen tables; ~1500 lines) that needs to be formatted into XML using FOR XML EXPLICIT. Unfortunately, the consumer of this file has demanded the use of CDATA tags, otherwise I would be using FOR XML PATH.

This is what I'm looking for:

<elem elem_att1="..." elem_att2="1" elem_att3="...">
    <a>...</a>            
    <b>...</b>
    <c>...</c>
    <d>...</d>
    <e>
        <e1 e1_att1="..." e1_att2="1">
            <e11>...</e11>
            <e12><![CDATA[...]]></e12>
            <e13><![CDATA[...]]></e13>
        </e1>
        <e1 e1_att1="..." e1_att2="2">
            <e11>...</e11>
            <e12><![CDATA[...]]></e12>
            <e13><![CDATA[...]]></e13>
        </e1>
        <e1 e1_att1="..." e1_att2="3">
            <e11>...</e11>
            <e12><![CDATA[...]]></e12>
            <e13><![CDATA[...]]></e13>
        </e1>
    </e>
    <f>
        <f1 f1_att1="..." f1_att2="..." />
        <f2 f2_att1="..." f2_att2="..." />
        <f3 f3_att1="..." f3_att2="..." />
    </f>
</elem>

Assume that the following tag and parent mappings have been properly defined in in the query:

      Tag | Parent
elem  2   | 1
a     3   | 2
b     4   | 2
c     5   | 2
d     6   | 2
e     7   | 2
e1    8   | 7
e11   9   | 8
e12   10  | 8
e13   11  | 8
f     12  | 2
f1    13  | 12
f2    14  | 12
f3    15  | 12

I've gotten about 90% of the way there with the query but am encountering an issue related to order of tags within the universal table. I would expect to see the following output for the first two columns of the table returned from the query:

Tag | Parent
2   | 1
3   | 2
4   | 2
5   | 2
6   | 2
7   | 2 <-- beginning of the "e" element
8   | 7 <-- first instance of the "e1" element
9   | 8
10  | 8
11  | 8
8   | 7 <-- second instance of the "e1" element
9   | 8
10  | 8
11  | 8
8   | 7 <-- third and final instance of the "e1" element
9   | 8
10  | 8
11  | 8
12  | 2 <-- beginning of the "f" element
13  | 12
14  | 12
15  | 12

Instead, I'm getting this as the first two columns of the query output:

Tag | Parent
2   | 1
3   | 2
4   | 2
5   | 2
6   | 2
7   | 2 <-- beginning of the "e" element
12  | 2 <-- beginning of the "f" element
13  | 12
14  | 12
15  | 12
8   | 7 <--first instance of the "e1" element
9   | 8
10  | 8
11  | 8
8   | 7 <-- second instance of the "e1" element
9   | 8
10  | 8
11  | 8
8   | 7 <-- third and final instance of the "e1" element
9   | 8
10  | 8
11  | 8

Which would obviously generate malformed XML along the lines of this:

<elem elem_att1="..." elem_att2="1" elem_att3="...">
    <a>...</a>            
    <b>...</b>
    <c>...</c>
    <d>...</d>
    <e />
    <f>
        <f1 f1_att1="..." f1_att2="..." />
        <f2 f2_att1="..." f2_att2="..." />
        <f3 f3_att1="..." f3_att2="..." />
    </f>
        <e1 e1_att1="..." e1_att2="1">
            <e11>...</e11>
            <e12><![CDATA[...]]></e12>
            <e13><![CDATA[...]]></e13>
        </e1>
        <e1 e1_att1="..." e1_att2="2">
            <e11>...</e11>
            <e12><![CDATA[...]]></e12>
            <e13><![CDATA[...]]></e13>
        </e1>
        <e1 e1_att1="..." e1_att2="3">
            <e11>...</e11>
            <e12><![CDATA[...]]></e12>
            <e13><![CDATA[...]]></e13>
        </e1>
    </e>
</elem>

The query chokes and throws the following error:

Msg 6833, Level 16, State 1, Line 2
Parent tag ID 7 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Here is my order by clause:

order by [elem!2!att2], [e1!8!att2]

If I change the order by clause to

order by [elem!2!att2], tag, [e1!8!att2]

the query executes successfully, but all child records of e1 elements are nested under the last e1 element:

<elem elem_att1="..." elem_att2="1" elem_att3="...">
    <a>...</a>            
    <b>...</b>
    <c>...</c>
    <d>...</d>
    <e>
        <e1 e1_att1="..." e1_att2="1" />
        <e1 e1_att1="..." e1_att2="2" />
        <e1 e1_att1="..." e1_att2="3">
            <e11>...</e11>
            <e11>...</e11>
            <e11>...</e11>
            <e12><![CDATA[...]]></e12>
            <e12><![CDATA[...]]></e12>
            <e12><![CDATA[...]]></e12>
            <e13><![CDATA[...]]></e13>
            <e13><![CDATA[...]]></e13>
            <e13><![CDATA[...]]></e13>
        </e1>
    </e>
    <f>
        <f1 f1_att1="..." f1_att2="..." />
        <f2 f2_att1="..." f2_att2="..." />
        <f3 f3_att1="..." f3_att2="..." />
    </f>
</elem>

The actual question:

What is causing f and its children to populate in the result set prior to the population of e's children?

I'm hoping this is a relatively common mistake, the solution to which is abstract enough to be relayed without having to copy over 1500 lines of code.

Answer 1

Long time since I've used this but have you tried

order by [elem!2!att2], [e], [e1!8!att2]

or

order by [elem!2!att2], 7, [e1!8!att2]