title: Export hierarchical data into XML
body: <p>Our database contains hierarchical "items". Every item has detail information inside a separate table. E.g. an item could be a folder or a file. Now I would like to export children of an item to XML. For performance reasons I would like to do this inside a SP. Of course, it's possible to write a cursor-based SP, but cursors tend to be slow. Much better are <strong>CTE</strong> (with ...) and <strong>FOR XML</strong>, but I don't know how to combine CTE and FOR XML including the detail table information.</p>
  
  <p>DDL</p>
  
  <pre><code>create table items (
     itemid integer,
     parent_itemid integer
  )
  
  create table folder (
     folderid integer,
     itemid integer,
     foldername varchar(50),
     [..]
  
  create table file (
     fileid integer,
     itemid integer,
     filename varchar(50),
     [..]
  </code></pre>
  
  <p>XML Export</p>
  
  <pre><code>&lt;folder id="1" foldername="Top-Folder"&gt;
   &lt;folder id="2" foldername="Sub-Folder"&gt;
     &lt;file id="10" filename="Subdoc.doc"/&gt;
   &lt;/folder&gt; 
   &lt;file id="10" filename="Doc1.doc"/&gt;
   &lt;file id="11" filename="Doc2.doc"/&gt;
  &lt;/folder&gt;
  </code></pre>