title: SQL for XML Path using data values for node names and nesting
body: <p>I have table data in the following format:</p>
  
  <pre><code>category  productkey productcode    productname  
  membership  1        055385        Professional
  membership  2        055575        Student
  membership  3        055370        Retired
  event       4        P055391       Annual Conference
  event       5        PM2018        Monthly Meeting
  </code></pre>
  
  <p>The following XML format is what Im looking to achieve, notice that the second level of XML nodes from the data:</p>
  
  <pre><code> &lt;Products&gt;
  &lt;Membership&gt;
      &lt;Product&gt;
           &lt;productcode&gt;055385&lt;/productcode&gt;
           &lt;productname&gt;Professional&lt;productname&gt;
      &lt;/Product&gt;
      &lt;Product&gt;
           &lt;productcode&gt;055575&lt;/productcode&gt;
           &lt;productname&gt;Student&lt;productname&gt;
      &lt;/Product&gt;
      &lt;Product&gt;
          &lt;productcode&gt;055370&lt;/productcode&gt;
          &lt;productname&gt;Retired&lt;productname&gt;
      &lt;/Product&gt;
  &lt;/Membership&gt;
  &lt;Event&gt;
      &lt;Product&gt;
           &lt;P055391&gt;055385&lt;/productcode&gt;
           &lt;productname&gt;Professional&lt;productname&gt;
      &lt;/Product&gt;
      &lt;Product&gt;
           &lt;productcode&gt;Annual Conference&lt;/productcode&gt;
           &lt;productname&gt;Monthly Meeting&lt;productname&gt;
      &lt;/Product&gt;
  &lt;/Event&gt;
  </code></pre>
  
  <p></p>
  
  <p>I have gotten close to the desired format based on this SQL: </p>
  
  <pre><code> select CAST('&lt;' + replace(b.category,' ','') + '&gt;' +
                   CAST( (select productcode, 
                                 productname
                           from @table a 
                           where a.productkey=b.productkey
                           for xml path ('Products'), TYPE) as varchar(max))
                    + '&lt;/' + replace(b.category,' ','') +'&gt;' as xml)
   from @table b  
   for xml path (''), ROOT('Product') 
  </code></pre>
  
  <p>This SQL is currently returning the "category" nodes on every product, instead of grouping the products under the "category" node:</p>
  
  <pre><code> &lt;Products&gt;
     &lt;Membership&gt;
       &lt;Product&gt;
         &lt;productcode&gt;055385&lt;/productcode&gt;
         &lt;productname&gt;Professional&lt;productname&gt;
       &lt;/Product&gt;
    &lt;/Membership&gt; 
    &lt;Membership&gt;   
       &lt;Product&gt;
         &lt;productcode&gt;055575&lt;/productcode&gt;
         &lt;productname&gt;Student&lt;productname&gt;
       &lt;/Product&gt;
    &lt;/Membership&gt; 
    &lt;Membership&gt; 
       &lt;Product&gt;
         &lt;productcode&gt;055370&lt;/productcode&gt;
         &lt;productname&gt;Retired&lt;productname&gt;
      &lt;/Product&gt;
    &lt;/Membership&gt;
  </code></pre>
  
  <p>Any help would be appreciated.
  thanks</p>