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.
