title: Selecting Single XML from Multiple XMLs rows in a Table
body: <p>I have an XML column in a table where each row of the table is a complete XML node.  I am trying to simply trying to select a subset of these rows and generate an XML document out of it with a root node.  I thought I could do the following, but it keeps added an extra wrapper around each node with the name of the XML column.  Is there anything different I can do to not get this wrapper?</p>
  
  <p>Sample Data Structure:</p>
  
  <pre><code>CREATE TABLE ActivityTable 
  (
      XMLDATA AS XML
  )
  
  INSERT INTO [ActivityTable] VALUES ( '&lt;Activity&gt;This is activity one&lt;/Activity&gt;' )
  INSERT INTO [ActivityTable] VALUES ( '&lt;Activity&gt;This is activity two&lt;/Activity&gt;' )
  INSERT INTO [ActivityTable] VALUES ( '&lt;Activity&gt;This is activity three&lt;/Activity&gt;' ) 
  </code></pre>
  
  <p>Query to get Data</p>
  
  <pre><code>SELECT 
      XMLdata FROM ActivityTable 
  FOR XML PATH(''), ROOT('RootNode')
  </code></pre>
  
  <p>What I'm getting:</p>
  
  <pre><code>&lt;root&gt;
    &lt;XMLdata&gt;
      &lt;Activity&gt;This is activity one&lt;/Activity&gt;
    &lt;/XMLdata&gt;
    &lt;XMLdata&gt;
      &lt;Activity&gt;This is activity two&lt;/Activity&gt;
    &lt;/XMLdata&gt;
    &lt;XMLdata&gt;
      &lt;Activity&gt;This is activity three&lt;/Activity&gt;
    &lt;/XMLdata&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>What I want:</p>
  
  <pre><code>&lt;root&gt;
    &lt;Activity&gt;This is activity one&lt;/Activity&gt;
    &lt;Activity&gt;This is activity two&lt;/Activity&gt;
    &lt;Activity&gt;This is activity three&lt;/Activity&gt;
  &lt;/root&gt;
  </code></pre>