Trying to convert XML to relational database file

[John_SC]

I have an XML file that has multi-value fields. I am trying to convert this file to a relational database without success. The various techniques I have tried (reading the file directly into SQL Server & Access) both capture the structure but does not populate the data.

An option I am considering is to write some code to parse the XML file and generate the relational file. This raises a questions.

1) Does anyone have any suggestions, tips or know of sample code, possible with System.xml?

Any guidance would be appreciated.

[patel45]

Do you want create a new database or export xml to a table of existing database ?

[John_SC]

Create a new one. Ideally I would like a system that would extract the structure from the xml file and create the database structure, then populate the tables with the data. As I indicated, doing a straight import to either Access or SQL Server appears to create the database structure, so I guess I could create the structure that way then import to that structure. An all in one procedure would be preferred.

[kpmc]

First I would read the XML into a dataset/table. from there iterate the columns and generate a CREATE TABLE sql command and run it. Once the table is created I would then bind a datatable to the newly created SQL table including a bindingsource. I would then iterate all the rows in the XML datatable and .addnew to the SQL bindingsource. You could also .merge the data from XML datatable to the SQL datatable but then you need to alter the rowstate of each row before a final adapter .update is called (adding the rows to the bindingsource prevents that).

If you want to take this route then create a form, drag a datagridview and a button. Run this from the button and hopefully the XML doesnt need work to get it into the datatable

    Private Sub ExportToSQLTable_Click(sender As Object, e As EventArgs) Handles ExportToSQLTable.Click
        Dim DS As New DataSet
        Dim XMLPath As String = "C:\DATA\XML\MgrData.XML"
        If IO.File.Exists(XMLPath) Then
            DS.ReadXml(XMLPath, XmlReadMode.InferSchema)
        End If
    End Sub