Here, the process gets a bit more difficult to understand. Unfortunately, Query Analyzer isn't much help because it doesn't color-highlight keywords for stored procedures as it does for normal T-SQL. One way around that and a useful technique for debugging is to use a PRINT @SQL statement in place of the EXEC (@SQL) statement. You can paste the SQL code into another Query Analyzer window and run it to see the syntax highlighting. This code has fewer joins because you loaded much of the data into the #Level table. The final step, which callout H shows, is to return the XML data.
Another Solution: XSLT
One of the main objectives behind the development of XML was to allow data exchange between different types of data stores. If you're writing stored procedures to export data from SQL Server, perhaps using Data Transformation Services (DTS), you'll want to convert the XML into the required target format by using SQL Server's XML support. The way Microsoft chose to implement XML in SQL Server doesn't make this conversion easy for some types of data structures, although it's achievable.
Many project architectures require a SQL Server—only solution for the production of XML. However, it's always important to look at the bigger picture. If, for instance, you have a Visual Basic (VB) front end or use Extensible Style Language Transformations (XSLT) to transform the data, it might be easier to return the XML data as "flat" (non-hierarchical) XML, then convert it to hierarchical XML elsewhere. To illustrate this technique, let's briefly look at a solution that uses XSLT.
To generate flat XML data, you can use a simple FOR XML RAW T-SQL statement, as Listing 6, page 29, shows. If you run this code in Query Analyzer, you get the XML that Figure 8, page 29, shows. Although the result isn't hierarchical XML, it contains the extra attribute ReportsToEmployeeID, which provides hierarchical information. XML parsers can't interpret this information directly—they treat it as just another attribute—so you need to convert the flat XML to hierarchical XML by using the ReportsToEmployeeID attribute.
XSLT's use of recursive techniques makes the conversion relatively easy. XSLT offers many ways, of varying efficiency, to convert the data. The code that Listing 7 shows is one possible, very short, way. If you use MSXML 3.0 to transform the XML in Figure 8 and the XSLT from Listing 7, you produce the XML structure that Figure 9 shows (tidied up a little for clarity).
The XSL has two templates. The first one matches all top-level nodes (i.e., all nodes in which ReportsToEmployeeID equals EmployeeID). The second template then recursively evaluates all other nodes. Note the addition of the <root> and </root> tags, which ensures that the XML is well formed. For more information about XSLT, see the book XSLT Quickly by Bob Ducharme (Manning, 2001).
Interesting Possibilities
Most developers will need to store hierarchical data in SQL Server at some time. I used an employee reporting structure here as an example, but you could use the techniques equally well for a bill of parts for a car or a dynamic menu system for a Windows application. You can use data stored in SQL Server to create hierarchical XML, either directly in SQL Server by using the built-in XML features or externally by using XSLT.