title: How to format XML using T-SQL
body: <p>I've got a table called StoreFolder that contains an XML field called FolderXml.
  Each row contains an XML value like the following:</p>
  
  <pre><code>&lt;Folder FolderName="test" StoreName="TestStore" /&gt;
  </code></pre>
  
  <p>I need to get the Folders in the following XML format:</p>
  
  <pre><code>&lt;Folders&gt;
    &lt;Folder FolderName="test1" StoreName="TestStore1" /&gt;
    &lt;Folder FolderName="test2" StoreName="TestStore1" /&gt;
    &lt;Folder FolderName="test3" StoreName="TestStore1" /&gt;
  &lt;/Folders&gt;
  </code></pre>
  
  <p>I've tried using the following SQL statement:</p>
  
  <pre><code>SELECT   FolderXml
  FROM     StoreFolder
  FOR XML PATH(''), ROOT('Folders')
  </code></pre>
  
  <p>But it outputs the following XML:</p>
  
  <pre><code>&lt;Folders&gt;
    &lt;FolderXml&gt;
      &lt;Folder FolderName="test1" StoreName="TestStore1" /&gt;
    &lt;/FolderXml&gt;
    &lt;FolderXml&gt;
      &lt;Folder FolderName="test2" StoreName="TestStore1" /&gt;
    &lt;/FolderXml&gt;
    &lt;FolderXml&gt;
      &lt;Folder FolderName="test3" StoreName="TestStore1" /&gt;
    &lt;/FolderXml&gt;
  &lt;/Folders&gt;
  </code></pre>
  
  <p>Can someone please show me how to get the XML in the following format (using T-SQL):</p>
  
  <pre><code>&lt;Folders&gt;
    &lt;Folder FolderName="test1" StoreName="TestStore1" /&gt;
    &lt;Folder FolderName="test2" StoreName="TestStore1" /&gt;
    &lt;Folder FolderName="test3" StoreName="TestStore1" /&gt;
  &lt;/Folders&gt;
  </code></pre>
  
  <p>Many thanks</p>