How to create XML/XSD from SQL table with multiple tables

Question

How to create XML/XSD from SQL table with multiple tables

what I want to do is create an XML schema from a database (or database schema)

This post has some interesting code

Particularly

DataSet results = new DataSet();

SqlCommand command = new SqlCommand("SELECT * FROM table", new SqlConnection(connectionString));

SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);

sqlAdapter.FillSchema(results, SchemaType.Mapped);//Fills dataset with schema from query
results.WriteXmlSchema(mySchema);

But how do I generate this XML schema from multiple tables? If I just do

Select * from table1,table2

All of the data is in one big blob in the XML

What I want is each table defined in the XML with it's contents separately. something

Answer 1

You could grab the schema for each table separately, and combine it using an XDocument. Here's a function to grab a schema from a database query:

XDocument GrabSchema(
   string dataSetName,
   string sql)
{
   var con = new SqlConnection("<<YourConnectionString>>");
   var command = new SqlCommand(sql, con);
   var sqlAdapter = new SqlDataAdapter(command);
   var dataSet = new DataSet(dataSetName);
   sqlAdapter.FillSchema(dataSet, SchemaType.Mapped);
   var stream = new MemoryStream();
   dataSet.WriteXmlSchema(stream);
   stream.Seek(0, System.IO.SeekOrigin.Begin);
   return XDocument.Load(XmlReader.Create(stream));
}

You then combine the two table schemas like:

var firstSchema = GrabDataset("t1", "select a = 49");
var secondSchema = GrabDataset("t2", "select b = '50'");
firstSchema.Root.Add(secondSchema.Root.Elements());

You can verify the combined schema makes sense by loading it into another DataSet:

DataSet dataSet = new DataSet();
dataSet.ReadXmlSchema(firstSchema.CreateReader());