Export hierarchical data into XML

Question

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 CTE (with ...) and FOR XML, but I don't know how to combine CTE and FOR XML including the detail table information.

DDL

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),
   [..]

XML Export

<folder id="1" foldername="Top-Folder">
 <folder id="2" foldername="Sub-Folder">
   <file id="10" filename="Subdoc.doc"/>
 </folder> 
 <file id="10" filename="Doc1.doc"/>
 <file id="11" filename="Doc2.doc"/>
</folder>

Answer 1

If the data will fit into memory, export the 3 tables (relevant chunks) into your favorite programming language, then assemble the tree. Or, dump the tables into text files, such as .CSV format, then scan those.

I would use Perl, but Ruby or Python would probably be good choices as well. Or your own language of choice / necessity.

Escape from the database, and the rest should be fairly straight forward. Other than the "moment of extraction" aspect, reporting dumps don't need transactions. Get the data out, then chew on it at your leisure.

Answer 2

Maybe this will help: Create XML Files Out Of SQL Server With SSIS And FOR XML Syntax