Recursive Hierarchies to XML with SQL Server 2005

Christian Wade's Blog - SQL Server Standard - Recursive Hierarchies to XML

"I wrote an article for the September issue of SQL Server Standard magazine:

The version that was finally published was a very cut down version with minimal code snippets. This was for obvious reasons; they had to fit the content onto small columns in the magazine. For the readers who would prefer the more verbose version, here it is! I guess this is further reference material for the published article; I’m sure my friends at SQL Server Standard won’t mind.

Suppose we have a sizeable recursive hierarchy in our SQL Server 2005 relational database. We want to export it to XML. This could be for a variety of reasons....

On the face of it, this seems like a simple nut to crack. However, there are various design options available to us. This article explores some of the options. Performance is deemed the overriding factor when evaluating the optimal approach.

..."

This is a cool article which looks at two approaches to providing hierarchal XML from a self-referencing SQL Server 2005 table (fish hook reference, parent - child keys, etc).