XML from MS SQL Server 2005 Using FOR XML PATH

To begin with...wow. A quick google search and ten minutes worth of reading can gain you so much. It sure doesn't take long to appreciate the changes Microsoft has made with respect to FOR XML PATH. My google search on "FOR XML PATH" returned a winner on the first hit. Michael Rys blog had exactly what I was looking for. At any rate, here is yesterday's work wrapped up into one select statement.

SELECT BaseTable.BaseTableId AS "baseId",

    BaseTable.BaseName AS "baseName",

    BaseTable.BaseTypeId AS "baseType/@baseTypeId",

    BaseTableType.BaseTableTypeName AS "baseType/text()"

FROM BaseTable

    LEFT OUTER JOIN BaseTableType ON BaseTable.BaseTableTypeId = BaseTableType.BaseTableTypeId

WHERE BaseTableId = 5

FOR XML PATH('baseTable')

The result is exactly what I wanted (and in much less time).

<baseTable>

 <baseId>5</baseId>

 <baseName>Name of Base</baseName>

 <baseType baseTypeId="1">A Type of Base</baseType>

</baseTable>

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.