FOR XML / maximum columns

I need to export some data as XML. I have a table (call it Parent) with a PK of ParentID (BIGINT).<br /><br />I have maybe 40 other tables that have ParentID as a field. Not all of these will have records for every ParentID. Some of these table would have additional sub tables.<br /><br />I need to export data in this format<br /><br /><pre><br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />arent ID="1"&gt;<br /> &lt;Table1&gt;<br /> &lt;element1&gt;value&lt;/element1&gt;<br /> &lt;element2&gt;value&lt;/element2&gt;<br /> &lt;/Table1&gt;<br /> &lt;Table2&gt;<br /> &lt;element1&gt;value&lt;/element1&gt;<br /> &lt;element2&gt;value&lt;/element2&gt;<br /> &lt;/Table2&gt;<br />&lt;/Parent&gt;<br />&lt<img src='/community/emoticons/emotion-4.gif' alt=';P' />arent ID="2"&gt;<br /> &lt;Table8&gt;<br /> &lt;element1&gt;value&lt;/element1&gt;<br /> &lt;element2&gt;value&lt;/element2&gt;<br /> &lt;table54&gt;<br /> &lt;element&gt;value&lt;/element&gt;<br /> &lt;/table54&gt;<br /> &lt;/Table1&gt;<br /> &lt;Table9&gt;<br /> &lt;element1&gt;value&lt;/element1&gt;<br /> &lt;element2&gt;value&lt;/element2&gt;<br /> &lt;/Table2&gt;<br />&lt;/Parent&gt;<br /></pre><br />The number of elements for each table might be as high as 50 or even a 100.<br /><br />Now I could do this using lots of SELECT statement with UNION ALL and FOR XML EXPLCIT but I'd end up with hundreds (maybe even a thousand) columns in each SELECT statement and it would be a right pain. I'd obviously have to auto generate the code for this.<br /><br />Firstly, is there a limit to the number of columns I can have in a SELECT statement?<br /><br />Secondly, is there a better way of doing this?

.NET DataSet to the rescue and I'm sorted. Loading all the tables into one of these and setting up relationships / XML column mapping then streaming xml to a file and I'm done.

I'd recommend it to anyone who needs to export large amounts of XML.