Listing 1: XML Document

<XML>
  <Category Name="Accessories">
    <SubCategory Name="Bike Racks" />
    <SubCategory Name="Bike Stands" />
    ...
    <SubCategory Name="Pumps" />
    <SubCategory Name="Tires and Tubes" />
  </Category>
  <Category Name="Bikes">
    <SubCategory Name="Mountain Bikes" />
    <SubCategory Name="Road Bikes" />
    <SubCategory Name="Touring Bikes" />
  </Category>
  <Category Name="Clothing">
    <SubCategory Name="Bib-Shorts" />
    <SubCategory Name="Caps" />
    ...
    <SubCategory Name="Tights" />
    <SubCategory Name="Vests" />
  </Category>
  <Category Name="Components">
    <SubCategory Name="Bottom Brackets" />
    <SubCategory Name="Brakes" />
    ...
    <SubCategory Name="Touring Frames" />
    <SubCategory Name="Wheels" />
  </Category>
</XML>

Listing 2: SQL 2000 FOR XML EXPLICIT

SELECT
  1           AS 'Tag'
  ,NULL       AS 'Parent'
  ,NULL       AS 'XML!1'
  ,NULL       AS 'Category!2!Name'
  ,NULL       AS 'SubCategory!3!Name'

UNION ALL

SELECT DISTINCT
  2           AS 'Tag'
  ,1          AS 'Parent'
  ,NULL       AS 'XML!1'
  ,Name       AS 'Category!2!Name'
  ,NULL       AS 'SubCategory!3!Name'
FROM Production.ProductCategory

UNION ALL

SELECT
  3           AS 'Tag'
  ,2          AS 'Parent'
  ,NULL       AS 'XML!1'
  ,c.Name     AS 'Category!2!Name'
  ,sc.Name    AS 'SubCategory!3!Name'
FROM Production.ProductCategory c
  INNER JOIN Production.
                  ProductSubCategory sc
    ON c.ProductCategoryID = 
       sc.ProductCategoryID

ORDER BY
  'Category!2!Name'
  ,'SubCategory!3!Name'

FOR XML EXPLICIT

Listing 3: FOR XML PATH

SELECT Name AS '@Name'
FROM Production.ProductCategory
FOR XML PATH

Listing 4: FOR XML PATH Results

<row Name="Accessories" />
<row Name="Bikes" />
<row Name="Clothing" />
<row Name="Components" />

Listing 5: Named Element

SELECT Name AS '@Name'
FROM Production.ProductCategory
FOR XML PATH('Category')

Listing 6: Named Element Results

<Category Name="Accessories" />
<Category Name="Bikes" />
<Category Name="Clothing" />
<Category Name="Components" />

Listing 7: SubCategory Example

SELECT Name AS '@Name'
FROM Production.ProductSubCategory
FOR XML PATH('SubCategory')

<SubCategory Name="Bib-Shorts" />
<SubCategory Name="Bike Racks" />
...
<SubCategory Name="Vests" />
<SubCategory Name="Wheels" />

Listing 8: Combining the Queries

SELECT c.Name AS '@Name',
  (SELECT sc.Name AS '@Name'
  FROM Production.ProductSubCategory sc
  WHERE sc.ProductCategoryID = 
        c.ProductCategoryID
  FOR XML PATH('SubCategory'), TYPE)
FROM Production.ProductCategory c
FOR XML PATH('Category')

Listing 9: Combined Results

<Category Name="Accessories">
  <SubCategory Name="Bike Racks" />
  <SubCategory Name="Bike Stands" />
  ...
  <SubCategory Name="Pumps" />
  <SubCategory Name="Tires and Tubes" />
</Category>
<Category Name="Bikes">
  <SubCategory Name="Mountain Bikes" />
  <SubCategory Name="Road Bikes" />
  <SubCategory Name="Touring Bikes" />
</Category>
<Category Name="Clothing">
  <SubCategory Name="Bib-Shorts" />
  <SubCategory Name="Caps" />
  ...
  <SubCategory Name="Tights" />
  <SubCategory Name="Vests" />
</Category>
<Category Name="Components">
  <SubCategory Name="Handlebars" />
  <SubCategory Name="Bottom Brackets" />
   ...
  <SubCategory Name="Touring Frames" />
  <SubCategory Name="Wheels" />
</Category>

Listing 10: Adding a Root Element

SELECT c.Name AS '@Name',
  (SELECT sc.Name AS '@Name'
  FROM Production.ProductSubCategory sc
  WHERE sc.ProductCategoryID = 
        c.ProductCategoryID
  ORDER BY sc.Name
  FOR XML PATH('SubCategory'), TYPE)
FROM Production.ProductCategory c
ORDER BY c.Name
FOR XML PATH('Category'), ROOT('XML')

Listing 11: Final Results

<XML>
  <Category Name="Accessories">
    <SubCategory Name="Bike Racks" />
    <SubCategory Name="Bike Stands" />
    ...
    <SubCategory Name="Pumps" />
    <SubCategory Name="Tires and Tubes" />
  </Category>
  <Category Name="Bikes">
    <SubCategory Name="Mountain Bikes" />
    <SubCategory Name="Road Bikes" />
    <SubCategory Name="Touring Bikes" />
  </Category>
  <Category Name="Clothing">
    <SubCategory Name="Bib-Shorts" />
    <SubCategory Name="Caps" />
    ...
    <SubCategory Name="Tights" />
    <SubCategory Name="Vests" />
  </Category>
  <Category Name="Components">
    <SubCategory Name="Bottom Brackets" />
    <SubCategory Name="Brakes" />
    ...
    <SubCategory Name="Touring Frames" />
    <SubCategory Name="Wheels" />
  </Category>
</XML>