title: SQL "For XML Path" - nested results
body: <p>I have this table structure.  YearPart, MonthPart and DatePart contain what they describe... EX: 2011, 1, 19 (respectively)</p>
  
  <pre><code>DECLARE @agenda AS TABLE    (
    PID INT IDENTITY(1,1) PRIMARY KEY,
    YearPart int,
    MonthPart int,
    DayPart int,
    lib_title nvarchar(200),
    [filename] nvarchar(255),
    meta_value nvarchar(2000)
  )
  </code></pre>
  
  <p>Using this sample data:</p>
  
  <pre><code>INSERT INTO @agenda VALUES (2010, 12, 4, 'Test Record', '', '')
  INSERT INTO @agenda VALUES (2011, 1, 3, 'Another Record', '', '')
  INSERT INTO @agenda VALUES (2011, 1, 3, 'Fred Birthday', '', '')
  INSERT INTO @agenda VALUES (2011, 1, 4, 'Work Day', '', '')
  INSERT INTO @agenda VALUES (2011, 12, 6, '2nd Test Record', '', '')
  </code></pre>
  
  <p>What I want, is an XML output like this:</p>
  
  <pre><code>&lt;root&gt;
    &lt;Year Year="2010"&gt;
      &lt;Month Month="12"&gt;
        &lt;Day Day="4"&gt;
          &lt;Item RecordName="Test Record" RecordID="1" /&gt;
        &lt;/Day&gt;
      &lt;/Month&gt;
    &lt;/Year&gt;
    &lt;Year Year="2011"&gt;
      &lt;Month Month="1"&gt;
        &lt;Day Day="3"&gt;
          &lt;Item RecordName="Another Record" RecordID="2" /&gt;
          &lt;Item RecordName="Geoffrey Birthday" RecordID="3" /&gt;
        &lt;/Day&gt; 
        &lt;Day Day="4"&gt;
          &lt;Item RecordName="Work Day" RecordID="4" /&gt;
        &lt;/Day&gt;
      &lt;/Month&gt;
      &lt;Month Month="12"&gt;
        &lt;Day Day="6"&gt;
          &lt;Item RecordName="2nd Test Record" RecordID="5" /&gt;
        &lt;/Day&gt;
      &lt;/Month&gt;
    &lt;/Year&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>So far, I haven't been able to get the nesting to work right.  I usually end up with the grouping off (for example, I get multiple Year=2011 elements, when there should only be one).</p>
  
  <p>If this can't be done, I can always create the XML on the .NET site...</p>