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>