In an effort to improve the performance of one of my applications, I have been researching the use of SQLXML for SQL Server 2000, specifically with the “For XML Explicit” option of the Select statement. The basics of this option are fairly easy to understand - you are creating a structured recordset that is being translated into an xml document. What I wanted to do, though, was to create a “collection” of child nodes, rather than having each child node listed directly under the parent.
For example, rather than having a document that looks like this:
<TABLE1 Field="”Data”">
<TABLE2 Field="”Data/">
<TABLE2 Field="”Data/">
</TABLE1>
I want to create an xml document that looks like this:
<TABLE1 Field="”Data”">
<CHILDREN>
<TABLE2 Field="”Data”" />
<TABLE2 Field="”Data”" />
</CHILDREN>
</TABLE>
In order to achieve this goal, let's assume that I have 3 tables in my database:
Table1
ID
Name
Table2
ID
Name
jT1T2
T1_ID
T2_ID
jT1T2 represents the many-to-many relationship, and also represents the “children” collection that we want to create in the XML document. Now let's start by creating our basic SQLXML query that will produce the first document example listed above.
Select
1 As Tag,
Null As Parent,
t1.ID As [Table1!1!ID],
t1.Name As [Table1!Name],
Null as [Table2!2!ID],
Null as [Table2!2!Name]
From
Table1 t1
Union All
Select
2,
1,
j.T1_ID,
Null,
t2.ID,
t2.Name
From
jT1T2 j,
Table2 t2
Where
j.T1_ID=t2.ID
Order By
[Table1!1!ID],
[Table2!2!ID]
For XML Explicit
At this point, we now have an XML document that it output like this:
<TABLE1 id=”data” Name="”Data”">
<TABLE2 id=”data” Name="”Data”" />
<TABLE2 id=”data” Name="”Data”" />
...
</TABLE1>
What we would like to do, though, is add the “Children” collection node to this document. In order to do this, we must add a third select statement, and an additional sorting field - in this case the new sort field will be the “Parent” tag, rather than an xml element.
Select
1 As Tag,
Null As Parent,
t1.ID As [Table1!1!ID],
t1.Name As [Table1!Name],
Null As [Children!2]
Null As [Table2!3!ID],
Null As [Table2!3!Name]
From
Table1 t1
Union All
Select
2,
1,
j.T1_ID,
Null,
Null,
Null,
Null
From
jT1T2 j
Union All
Select
3,
2,
j.T1_ID,
Null,
Null,
t2.ID,
t2.Name
From
jT1T2 j,
Table2 t2
Where
j.T1_ID=t2.ID
Order By
[Table1!1!ID],
[Table2!2!ID],
Parent
For XML Explicit
Adding the [Children!2] node to the list of fields returned, and not giving it any values will create a XML node in our output that is called “Children”, but does not have any data directly in it. In order to maintain the proper sorting of data for the proper hierarchy in the XML document, we have to continue moving the fields that are used for sorting, through each of the select statements that we are using, otherwise we will end up with an error message stating that “Tag number x is not open”. And finally, sorting by the “Parent” field at the bottom of the list will ensure that the “Children” node is properly placed in the hierarchy, rather than being placed outside of Table1.
The end results of this query are:
<TABLE1 id=data Name="data">
<CHILDREN>
<TABLE2 id=data Name="data" />
<TABLE2 id=data Name="data" />
..
</CHILDREN>
</TABLE1>
<TABLE1>...</TABLE1>
...
Additional Links