title: Create single XML from SQL Server multiple tables query
body: <p>I would like to query multiple tables from SQL Server 2005 and create a single XML document and do this in a stored procedure.</p>
  
  <p>I know that I can query multiple tables within a stored procedure and get a <code>DataSet</code> in my .NET application that can be easily saved as XML. However, I'm trying to do something similar within the context of a stored procedure.</p>
  
  <p>Essentially I want to do something like this:</p>
  
  <pre><code>declare @x xml
  select @x = x.result
  from (select y.* from tabley y for xml path('y')
        union
        select a.* from tablea a for xml path('aa')
       ) as x
  select @x
  </code></pre>