title: Nesting of FOR XML Expressions
body: <p>I tried a FOR XML query similar to what's shown <a href="http://msdn.microsoft.com/en-us/library/ms345137%28v=sql.90%29.aspx" rel="nofollow">here</a> under the subtitle 'Nesting of FOR XML Expressions'. My problem is that I have to have three XML element levels. The results should be something like this.      </p>
  
  <pre><code>&lt;StepTree Name="ALFKI"&gt;
    &lt;Step Name="Foo"&gt;
      &lt;Result id="123" /&gt;
      &lt;Result id="456" /&gt;
    &lt;/Step&gt;
    &lt;Step Name="Bar"&gt;
      &lt;Result id="789" /&gt;
      &lt;Result id="987" /&gt;
    &lt;/Step&gt;
  &lt;/StepTree &gt;
  </code></pre>
  
  <p>I tried this type of query.</p>
  
  <pre><code>SELECT 1 as TAG,
         NULL as Parent,
         StepTrees.Name AS [StepTree!1!Name],
         NULL as [Step!2!Name],
         NULL as [Result!3!id]
  FROM StepTrees
  WHERE StepTrees.Name = 'ALFKI' 
  UNION ALL
  SELECT 2,
         1,
         StepTrees.Name,
         Steps.Name,
         NULL
  FROM Steps
  JOIN StepTrees ON Steps.StepTreeId = StepTrees.Id
  WHERE StepTrees.Name = 'ALFKI' 
  UNION ALL
  SELECT DISTINCT 3,
         2,
         StepTrees.Name,
         Steps.Name,
         Results.id
  FROM StepTrees
  JOIN Steps ON Steps.StepTreeId = StepTrees.Id
  JOIN Results ON Steps.StepId = Results.StepId
  FOR XML EXPLICIT
  </code></pre>
  
  <p>The resulting XML is as follows.</p>
  
  <pre><code>&lt;StepTree Name="ALFKI"&gt;
    &lt;Step Name="Foo" /&gt;
    &lt;Step Name="Bar"&gt;
      &lt;Result id="123" /&gt;
      &lt;Result id="456" /&gt;
      &lt;Result id="789" /&gt;
      &lt;Result id="987" /&gt;
    &lt;/Step&gt;
  &lt;/StepTree &gt;
  </code></pre>
  
  <p>Any ideas?</p>