Query to generate Single XML from multiple tables in SQL Server

Here is the sample code to generate a single xml from multiple table query in Sql Server. In this example I am querying data from two tables.

Sample Query :

SELECT
    (SELECT * FROM dbo.TableA FOR XML PATH('TableA'), TYPE),
    (SELECT * FROM dbo.TableB FOR XML PATH('TableB'), TYPE)
FOR XML PATH(''), ROOT('root')

XML Output :

<root>
   <TableA>
	 <Column1>Value1</Column1>
    	 <Column2>Value2</Column2>
   </TableA>
   <TableB>
	 <Column1>Value1</Column1>
    	 <Column2>Value2</Column2>
   </TableB>
</root>

You can load this Xml into DataSet using below C# code.

DataSet ds = new DataSet();
ds.ReadXml("queryResult.xml");