XML from MS SQL Server 2005 Using FOR XML EXPLICIT

Lately I have been working on extracting content from an MSSQL 2005 Server in an XML format. Starting with Query Analyzer, I have been building up my FOR XML EXPLICIT statement. Here is the path I have taken:

SELECT 1 AS Tag, NULL AS Parent, BaseTable.BaseTableId AS [baseRoot!1!baseId]

FROM BaseTable WHERE BaseTableId = @BaseTableId

FOR XML EXPLICIT

The result of this statement is the following xml:
Next, I wanted to make the ID an element instead of an attribute. This is easily accomplished by exchanging [BaseRoot!1!BaseTableId] with [baseRoot!1!baseId!ELEMENT]. Now I have the following xml document:

<baseRoot>

 <baseId>5</baseId>

</baseroot>

My next step is to add additional fields to my base table configuration. So in order to add a baseName field, here is my new select statement:

SELECT 1 AS Tag,

    NULL AS Parent,

    BaseTable.BaseTableId AS [baseRoot!1!baseId!ELEMENT],

    BaseTable.BaseName AS [baseRoot!1!baseName!ELEMENT]

FROM BaseTable WHERE BaseTableId = @BaseTableId

FOR XML EXPLICIT

And my result is as expected:

<baseRoot>

 <baseId>5</baseId>

 <baseName>NewName</baseName>

</baseRoot>

Next we need to dive a bit deeper and add a foriegn key relationship for BaseType. I updated my statement as follows:

SELECT 1 AS Tag,

    NULL AS Parent,

    BaseTable.BaseTableId AS [baseRoot!1!baseId!ELEMENT],

    BaseTable.BaseName AS [baseRoot!1!baseName!ELEMENT],

    BaseTableType.BaseTableTypeName AS [baseRoot!1!baseType!ELEMENT]

FROM BaseTable LEFT OUTER JOIN BaseTableType ON

    BaseTable.BaseTableTypeId = BaseTableType.BaseTableTypeId

WHERE BaseTableId = @BaseTableId

FOR XML EXPLICIT

Again, my result is as follows:

<baseRoot>

 <baseId>5</baseId>

 <baseName>NewName</baseName>

 <baseType>Type A</baseType>

</baseRoot>

Now is where I started to get into trouble. Instead of having a separate level to model type, I want to add an attribute to view the item as follows: <baseType baseTypeId="1">Type A</baseType>. Well, in my research, this can only be done with a Union and setting of a second level node. So the code would look something like this:

SELECT 1 AS Tag,

    NULL AS Parent,

    BaseTable.BaseTableId AS [baseRoot!1!baseId!ELEMENT],

    BaseTable.BaseName AS [baseRoot!1!baseName!ELEMENT],

    NULL AS [baseType!2!baseTypeId],

    NULL AS [baseType!2!baseTypeName!ELEMENT]

FROM BaseTable LEFT OUTER JOIN BaseTableType ON

    BaseTable.BaseTableTypeId = BaseTableType.BaseTableTypeId

WHERE BaseTableId = @BaseTableId

UNION ALL

SELECT 2,

    1,

    BaseTable.BaseTableId,

    BaseTable.BaseName,

    BaseTable.BaseTableTypeId,

    BaseTableType.BaseTableType

FROM BaseTable LEFT OUTER JOIN BaseTableType ON

    BaseTable.BaseTableTypeId = BaseTableType.BaseTableTypeId

WHERE BaseTableId = @BaseTableId

FOR XML EXPLICIT

That doesn't give me exactly what I want, but it is close enough for now. I'm sure it would only take a little more education and tweaking on my part to get what I want from the EXPLICIT statement. However, I can see that as I add complexity to my fetch statement the EXPLICIT statement becomes more and more painful. Instead, I'm going to investigate nested FOR XML statements and finally FOR XML PATH queries. I will post on those topics soon.