FOR XML EXPLICIT

With AUTO and RAW modes, while fairly easy to use, the user had no real control over the form of the XML results returned by the query.  With the EXPLICIT mode the user has the ability to specify how the results will look and offers much more flexibility over the AUTO and RAW modes.  There is some responsibility that the user needs to assume when using EXPLICIT mode by ensuring that the XML is well formed when generated.

FOR XML EXPLICT mode works by transforming the results from a recordset into an XML document.  This XML document has a format that is determined but the way the SELECT query is written.  This is where all the work comes in.  The user must determine what their results need to look like and then write the SELECT query to return those results.

The query needs to written so that a recordset produces something called a Universal Table in which the XML document is generated.  This table is not an actual SQL table but an In-Memory table whose sole purpose is to hold the XML document.  The query using the EXPLICIT mode requires two columns of meta data.  The first column in the query needs to be a named tag (Tag) number, of an integer type and has the responsibility of storing the tag number of the current element.  The second column needs to be a named (Parent) tag number.  This column lists the tag number of the parent element and is also an integer type. 

Huh?  Exactly.  A bit confusing isn’t it?  Here is an example to clarify things.  The following example lists all the orders (no order detail) for every sales person.  The two table names could be Employee and Orders, for example.  In Universal Table format the layout would look like this:

The Tag column is required to be the first column in a FOR XML EXPLICIT query.  It is metadata only and does not correspond to a physical database column.  It is for level definition only, meaning that it defines what level in the hierarchy tree this record is located.

The Parent column is required to be the second column in a FOR XML Explicit query.  It also does not correspond to a physical database column.  This column represents the tag number of an elements parent.  Every element has a Parent, even if the value of a Parent is NULL.  This is possible because the top level of a hierarchy has no Parent.  In the above example there are two records that have no Parent because they are at the top of the hierarchy level.

The rest of the columns DO correspond to a physical database column.  It contains the column value from the database value.

The general format for representing this information looks like this:

         ElementName!TagNumber!AttributeName!Directive

For every FOR XML EXPLICIT query, this alias is required for each column in the SELECT statement.   

In our example above, the Tag 1 is the primary table in this example so its tag number is 1.  The parent column is NULL because it is the primary table.  The next three rows have a tag number of 2 and a parent column value of 2 because it is the secondary table and is linked to the primary table.  It is then repeated for the next sales person and so on.

Let’s modify our first example and add a third table.  The layout would change a bit:

This example is not that different from the first.  A third table was added (OrderDetail) and the Parent of those rows is Tag 2 (rows 3, 4 and 6, 7 respectively)

It is time to deal with actual data and real examples.  In Query Analyzer type and execute the following query:

         SELECT       123         AS Tag,

                              0         As Parent,

                              RiderName   As [RiderTable!123!RiderName]

         FROM           Rider

         FOR XML EXPLICIT

The results from this query will look as follows:

         <RiderTable RiderName=”Damon Bradshaw”>

         <RiderTable RiderName=”Jeff Emig”>

         <RiderTable RiderName=”Jeremy McGrath”>

         <RiderTable RiderName=” Ricky Carmichael”>

         <RiderTable RiderName=”Travis Pastrana”>

         <RiderTable RiderName=”Kevin Windham”>

         <RiderTable RiderName=”Ezra Lusk”>

         <RiderTable RiderName=”John Dowd”>

         <RiderTable RiderName=” David Vuillemin”>

         :

         :

Although this example is only one table and fairly simple, it should start to give you an idea of how it works and basic requirements.  Let’s throw another table into the mix.  Change the query to look like this:

SELECT 1            as tag,

               0            as parent,

            sponsor as [TeamTable!1!Sponsor],

            NULL            as [RiderTable!2!RiderName]

FROM Team

Union All

SELECT 2,

               1,

               Sponsor,

               RiderName

FROM            Team, Rider

WHERE Team.TeamID = Rider.TeamID

ORDER BY [TeamTable!1!Sponsor],

            [RiderTable!2!RiderName]

FOR XML EXPLICIT

Internally, this will generate a Universal Table like the one used in the first example above.  It would look like this:

We could go even deeper and add a third table (the Class) table and break it down even further but the concept should be clear as to what is being accomplished.

Going back to the alias format you will notice that there is an optional part that we have not been using in any of our queries, and that is the Directive.  The Directive attribute determines how string data is handled in XML.

There are 5 options for the Directive attribute and they are:

-          cdata – this causes the column data to be placed in a CDATA section of an XML document.  This data must be a ntext, nvarchar, text, or varchar datatype.  If this directive is used then the attribute name cannot be used (i.e.,  [RiderTable!1!Sponsor!cdata] is  not valid.  [RiderTable!1!!cdata] is valid).

-          element – causes the column data to be specified as an element instead of an attribute.

-          xml – identical to the element directive except that the data is not encoded whereas the data is encoded when the element directive is used.

-          hide – any column that has the hide directive is not included in the XML document.

-          xmltext – handles overflow.  Used in conjunction with OPENXNL, when there are more elements or attributes than table columns, these extra elements and attributes are sent into a column (created by OPENXML) to deal with the overflow of data.  The xmltext directive fetches this extra data from this column.

Let’s take a minute and do a couple of examples using these directives.  The first one we’ll do is the hide directive.  Using the earlier example let’s make the following changes:

SELECT 1            as tag,

0               as parent,

Team.TeamID as [TeamTable!1!TeamID!hide],

            sponsor as [TeamTable!1!Sponsor],

            NULL            as [RiderTable!2!RiderName]

FROM Team

Union All

SELECT 2,

               1,

               Rider.TeamID,

               Sponsor,

               RiderName

FROM            Team, Rider

WHERE Team.TeamID = Rider.TeamID

ORDER BY [TeamTable!1!TeamID!hide],

            [RiderTable!2!RiderName]

FOR XML EXPLICIT

Even though the TeamID column is included in the SELECT statement it won’t show up in the results because of the hide directive added.

The next example we’ll look at uses the element directive.  Using our very first example, let’s modify it to look like the following:

         SELECT       123         AS Tag,

                              0         As Parent,

                              RiderName   As [RiderTable!123!RiderName!element]

         FROM           Rider

         FOR XML EXPLICIT

This causes our column data to be listed inside an element rather than an attribute, shown as follows:

         <RiderTable>

                  <RiderName>Damon Bradshaw</RiderName>

         </RiderTable>

         <RiderTable>

                  <RiderName>Jeremy McGrath</RiderName>

         </RiderTable>

The import thing to remember with all of this is that you can have complete control over how your results are returned to you.  The power and flexibility provided with the FOR XML can be overwhelming but don’t let it be intimidating.  With this information you can be doing FOR XML in no time at all.