title: Return Nested XML for repeated columns
body: <p>I have a table that looks like this, put code appropriately (particularly XML structures and queries) </p>
  
  <pre><code>Class Subclass   Information  Details
  Classx subclassx Info1        otherinfo1 
  Classx subclassx Info2
  Classx subclassy info3 
  Classyy subclassyyy info3 
  </code></pre>
  
  <p>As an example, I need to return rows filtered with Class='classx', returning:</p>
  
  <pre><code>&lt;Class Name="Classx"&gt;
    &lt;Subclass subclassName ="subclassx"&gt;
      &lt;Info&gt;Info1&lt;/Info&gt;
      &lt;Info&gt;Info2&lt;/Info&gt;
    &lt;/Subclass&gt;  
   &lt;Subclass subclassName ="subclassy"&gt;
      &lt;Info&gt;Info3&lt;/Info&gt;
    &lt;/Subclass&gt;  
  &lt;/Class&gt;
  </code></pre>
  
  <p>(or)</p>
  
  <pre><code>&lt;Class Name="Classx"&gt;
    &lt;Subclass&gt;subclassx
      &lt;Info&gt;Info1&lt;/Info&gt;
      &lt;Info&gt;Info2&lt;/Info&gt;
    &lt;/Subclass&gt;
   &lt;Subclass&gt;subclassy
     &lt;Info&gt;Info3&lt;/Info&gt;
   &lt;/Subclass&gt;
  &lt;/Class&gt;
  </code></pre>
  
  <p>Something that nests 'Subclass' inside 'Class' element, and 'Info' inside 'Subclass' element.</p>
  
  <p>Some queries attempted:</p>
  
  <pre><code>DECLARE @classFilter nvarchar(15);
  SELECT @classFilter = 'Classx' 
  
       SELECT @classFilter as '@Class',
                (SELECT  r1.subClass as 'SubClass',
                 (SELECT  DISTINCT r1.Info as 'Info' 
                     FROM @results as r2 
                     WHERE r2.Class=  @classFilter and r1.Subclass = r2.Subclass
                      FOR XML PATH('Information'), TYPE
                      )
                 FROM @results r1 
                GROUP BY r1.Class, r1.SubClass, r1.Info
                HAVING r1.Class= @classFilter
                  FOR XML PATH('Subclasses'), TYPE)
             FOR XML PATH('Class'), TYPE;
  </code></pre>
  
  <p>gives:</p>
  
  <pre><code>&lt;Class Class="Classx"&gt;
    &lt;Subclasses&gt;
      &lt;SubClass&gt;subclassx&lt;/SubClass&gt;
      &lt;Information&gt;
        &lt;Info&gt;Info1&lt;/Info&gt;
      &lt;/Information&gt;  
    &lt;/Subclasses&gt;
   &lt;Subclasses&gt;
      &lt;SubClass&gt;subclassx&lt;/SubClass&gt;
      &lt;Information&gt;
        &lt;Info&gt;Info2&lt;/Info&gt;
      &lt;/Details&gt;  
    &lt;/Subclasses&gt;
  &lt;Subclasses&gt;
      &lt;SubClass&gt;subclassy&lt;/SubClass&gt;
      &lt;Information&gt;
        &lt;Info&gt;Info3&lt;/Info&gt;
      &lt;/Information&gt;  
    &lt;/Subclasses&gt;
  &lt;/Class&gt;
  </code></pre>
  
  <p>The closest I am able to come is</p>
  
  <pre><code> SELECT @classfilter as '@Name',    --or r1.Class
          (SELECT r1.Subclass as '@Subclass', 
              (SELECT DISTINCT r1.Information as 'Information' 
                 FROM @results as r2 
                 WHERE r2.Class =  @classFilter and r1.Subclass = r2.Subclass         
                  )
             FROM @results r1           
            GROUP BY r1.Class, r1.Subclass, r1.Information
            HAVING r1.Class= @classFilter 
              FOR XML PATH('Subclasses'), TYPE)
         FOR XML PATH('Class'), TYPE;
  </code></pre>
  
  <p>Gives:</p>
  
  <pre><code> &lt;Class Name="classx"&gt;
        &lt;Subclasses subclass="subclassx"&gt;Info1&lt;/Subclasses&gt;
        &lt;Subclasses subclass="subclassx"&gt;Info2&lt;/Subclasses&gt;
        &lt;Subclasses subclass="subclassy"&gt;Info3&lt;/Subclasses&gt;
      &lt;/Class&gt;
  </code></pre>
  
  <p>Wondering what I'm missing!</p>