Newbie: creating DB tables from XML.

[BLUE]

Using Visual C# 2005, how can I programmatically create physical tables in a
DBMS from an XML schema or an XML file?
The only way is manually parsing the file to generate the "CREATE TABLE" SQL
statement?

Thanks in advance,
Luigi.

 

[Bruce Wood]

Using Visual C# 2005, how can I programmatically create physical tables in a
DBMS from an XML schema or an XML file?
The only way is manually parsing the file to generate the "CREATE TABLE" SQL
statement?

Use ADO.NET to read the XML into an ADO DataSet. If the XML has an XML
Schema, then you can use that to first create the table(s) in the
DataSet with all of the columns having appropriate types.*

Then use the ADO DataSet to create tables in the DBMS.

* If you don't have a schema, your biggest problem will be determining
the types of the data columns. For example, if an XML attribute says
code="50234", is that an alphanumeric field, or an integer? How big
can it get? etc. The XML Schema could give you answers to these
questions, allow ADO.NET to create data columns with the correct
types, and make the job of creating the tables in the DBMS much
simpler. Without the schema, you might be forced to make every column
a varying length string column.

 

[BLUE]

Then use the ADO DataSet to create tables in the DBMS.

Parsing the Dataset (I don't know how) and then using "CREATE TABLE"
statements or how?
Sorry if it's a stupid question!

Thanks,
Luigi.

 

[Bruce Wood]

Parsing the Dataset (I don't know how) and then using "CREATE TABLE"
statements or how?
Sorry if it's a stupid question!

No, it's not a stupid question. Once you have a DataSet full of
DataTables, you can just iterate over the DataTables, and for each
DataTable iterate over its DataColumns. Each DataColumn has a type,
and maybe a length (or other such information). This way you can
easily build the appropriate CREATE TABLE commands in order to create
the tables in the database.

 

[BLUE]

Thank you very much for your help!

 

[Guest]

Hello,

You xan use XMLBulkload component
(http://msdn2.microsoft.com/en-us/library/ms171993.aspx) and use the
SchemaGen property to generate the DB tables.

I hope this helps.

Regards,