title: Add Attribute to ElementNode created by TSQL FOR XML Path
body: <p>I am trying to match some XML output in a SQL Server 2008 TSQL query.  The XML I am matching can return Lists of items.  The lists are identified by attribute name - not the node name.  </p>
  
  <p>The XML Path functions will return a wrapper node but I can't find a way to add any attributes to that node.  In my case they will be hard coded values based on the table name I am running they select against.</p>
  
  <p>I have the 'x' as 'x' in there to keep the two lists separated.  In my actual data that is not a problem as these are in different nodes.  The problem is just how to get attributes added to the "List" node.</p>
  
  <p>Here is a <a href="http://sqlfiddle.com/#!3/395fa/3" rel="noreferrer">SQL Fiddle page for the example below</a>:</p>
  
  <p>Sample Schema  </p>
  
  <pre><code>create table Table1 (Value varchar(50)); 
  create table Table2 (Value varchar(50)); 
  
  insert Table1 values
  ('A'), ('B'), ('C');
  
  insert Table2 values
  ('X'), ('Y'), ('Z');
  </code></pre>
  
  <p>Sample Select  </p>
  
  <pre><code>select 
  (
   select Value as '@I'
   from Table1
   for XML PATH('L'), TYPE
  ) as List,
  'x' as 'x', -- needed to keep the Lists apart.
  (
   select Value as '@I'
   from Table2
   for XML PATH('L'), TYPE
  ) as List
  
  for XML PATH
  </code></pre>
  
  <p>Actual Output  </p>
  
  <pre><code>&lt;row&gt;
    &lt;List&gt;
      &lt;L I="A"/&gt;
      &lt;L I="B"/&gt;
      &lt;L I="C"/&gt;
    &lt;/List&gt;
  
    &lt;x&gt;x&lt;/x&gt;
  
    &lt;List&gt;
      &lt;L I="X"/&gt;
      &lt;L I="Y"/&gt;
      &lt;L I="Z"/&gt;
    &lt;/List&gt;
  &lt;/row&gt;
  </code></pre>
  
  <p>Desired Output: (Adding the "Name" attribute to the list wrapper.)</p>
  
  <pre><code>&lt;row&gt;
    &lt;List Name='Table1'&gt;  &lt;!-- Added Attribute "Name" here --&gt;
      &lt;L I="A"/&gt;
      &lt;L I="B"/&gt;
      &lt;L I="C"/&gt;
    &lt;/List&gt;
  
    &lt;x&gt;x&lt;/x&gt;
  
    &lt;List Name='Table2'&gt;  &lt;!-- Added Attribute "Name" here --&gt;
      &lt;L I="X"/&gt;
      &lt;L I="Y"/&gt;
      &lt;L I="Z"/&gt;
    &lt;/List&gt;
  &lt;/row&gt;
  </code></pre>