Getting XML Schema from MS SQL Database

Question

Is it possible to generate a XML Schema of a Database programatically with .Net and C#? I want to look into NDbUnit but for big databases it would not really be feasible to make a Schema manually?

Answer 1

Is it possible to generate a XML Schema from a Database?

It sure is, XMLSpy can generate XML Schema from a database.

There's another way, though I've never tested it:

create table Person
(
Age int not NULL check( Age > 0) ,
Height numeric(10,2) not NULL check( Height > 5),
Gender varchar(5) not null check( Gender in ('M', 'F', 'O')),
BirthDate datetime null,
)

DECLARE @schema xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA('PersonSchema'))
select @schema

Answer 2

I could do it like this:

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);

Problem is, how could I adapt this method so that it could be used with a indeterminate amount of tables? Without building up a string though concatenation which is not exactly ideal!