Generating XML from Relational Tables

Visual Studio 2010 / SQL server 2008

I have two tables for parent xml and child xml and they are related by key. I am trying to generate a XML string by using Linq(or anything works) and those two tables.

Parent xml data : Country Child xml data : USA, Mexico

I want to generate it like this way.

<Country>
   <USA>
   </USA>
   <Mexico>
   </Mexico>
</Country>

I have been tried with few ways b开发者_Python百科ut I could not figure out. Does anybody has suggestion?

Thanks.

The WriteXml method from the DataSet class should get you pretty close to what you want, and it's a simple thing to do.

In C#, it's:

private void WriteXmlToFile(DataSet thisDataSet) 
{
   // Create a file name to write to.
   string filename = "myXmlDoc.xml";

   // Create the FileStream to write with.
   System.IO.FileStream myFileStream 
       = new System.IO.FileStream (filename, System.IO.FileMode.Create);

   // Write to the file with the WriteXml method.

   thisDataSet.WriteXml(myFileStream);   
}

So all you have to do is load up your DataSet with the appropriate data, and the WriteXml method should generate a fairly appropriate XML representation of that data.