Writing multiple SQL server tables as separate nodes to XML Document -

i have multiple sql server tables want pull data , write xml. want xml formed this:

 <data>      <query1table>           <table>               <column1>data</column1>               <column2>data</column2>               ...           </table>       </query1table>       <query2table>           <table>               <column1>data</column1>               <column2>data</column2>               ...           </table>       </query2table>    </data> 

i'm using datasets write xml, code i'm working doesn't append data, overwrites:

        dim connetionstring string         dim connection sqlconnection         dim adapter sqldataadapter         dim directory string         dim ds new dataset         dim sql string          connection = new sqlconnection(connetionstring)         sql = "select * scheduledata"         connection.open()         adapter = new sqldataadapter(sql, connection)         adapter.fill(ds)          ds.datasetname = "schedule"         ds.writexml(directory)          ds.clear()         sql = "select * costdata"         adapter = new sqldataadapter(sql, connection)         adapter.fill(ds)         ds.writexml(directory) 

i have tried adding same data set calling sql queries @ once, doesn't separate them in xml -- groups them in same node.

i'm open different method if has suggestion.

you've defined directory string, ds.writexml (create and) write file name.

use filestream or xmltextwriter instead.