none
How to create database Schema and populate database from dataset populated by XML file based on XML Schema RRS feed

  • Question

  •  

    I have an XML file described by an XSD schema.  The xsd contains the details of about eight to ten tables and their relationships.  The XML file contains an attribute centric description of the data in those eight to ten tables.   This data schema is subject to change.   These xml and xsd files are periodically reissued.  While I can manually retrieve the schema and write it to the database and then import the xml files, I am designing an automated means for a customer with no database knowledge or skills to import this data using VB.Net, and convert it to ArcGIS feature classes for use in their GIS applications using VB.net with ArcObjects.

     

    I can read the schema and the data into an ADO.Net Dataset object.  However, I can not get it to write to the empty database.  I then tried creating a temporary Dataset with a connection to the database provided by a Selectcommand "Select * from sysObjects where 1 = 2".  I tried to copy the one dataset to the other.  This was not successful.  (I am using the oledb provider vice the SQLClient provider because, although I am creating the preliminary design using SQL Server, the real product must be built using Oracle.)

     

    Code Snippet

    Dim cmdString As String = "Select * from SysObjects Where 1 =2"

    Dim cmdDSXML As New System.Data.OleDb.OleDbCommand

     Call makeConn()

     Me.SQL_CN.Open()

     cmdDSXML.CommandText = cmdString

     cmdDSXML.Connection = Me.SQL_CN

     daDSXML.SelectCommand = cmdDSXML

     daDSXML.Fill(dsXML)

     

    ' Reading DSRead.XML file into DataSet Ds

     Ds = xmlDoc.DataSet

     Ds.ReadXmlSchema(Server.MapPath("App_Data\DEX403.xsd"))

     xmlDoc.Load(Server.MapPath("App_Data\9004T.xml"))

     

     daDSXML.AcceptChangesDuringUpdate = True

     

     

     

    Then I proceeded to try add the tables from the one dataset to the other.   This was not permitted.  I then proceeded to create a new datatable for each of the tables in the original dataset make a copy of each of the colums in the columns collection of each table, then add a new row with a copy of the data in each of the original rows.  (This would not really be what is desired because I would lose and have to recreate all my relationships.)

     

     

    Then I proceeded to update this dataset.  still nothing.  the database is still as blank as ever. 

     

    Code Snippet

    For i = 0 To Ds.Tables.Count - 1

    Dim dtTemp As New DataTable

    dtTemp.TableName = Ds.Tables(i).TableName

    For k = 0 To Ds.Tables(i).Columns.Count - 1

    dtTemp.Columns.Add(Ds.Tables(i).Columns(k).ColumnName, Ds.Tables(i).Columns(k).DataType)

    Next

     

    For j = 0 To Ds.Tables(i).Rows.Count - 1

    If Ds.Tables(i).Rows.Count > 0 Then

    Dim drTemp As DataRow = dtTemp.NewRow

    For k = 0 To Ds.Tables(i).Columns.Count - 1

    If Ds.Tables(i).Rows(j).IsNull(k) Then

    drTemp(k) = DBNull.Value

    Else

    drTemp(k) = Ds.Tables(i).Rows(j).Item(k)

    End If

    Next

    dtTemp.Rows.Add(drTemp)

    End If

    Next

    dsXML.Tables.Add(dtTemp)

    Next

    daDSXML.Update(dsXML)

     

     

    All the help I've have seen in various books, internet articles, and forums always seem to pre-suppose two assumptions.

    1.  The XML file and the schema file only describe one table, and

    2.  There is an instance of the table in the database with  at least zero rows.

     

    Neither of these things is true.  I need to create the schema from the XML schema contained in the dataset. 

    This is becoming very frustrating.  What is the point of having a xml schema if you can't use it to transport your data structure to a new location?  What is the point of a disconnected Dataset if you can't use it to generate new database structures? If your data structure already exists all you need is the contents; like a delimited text file.  Thank you in advance for your assistance. 


    Thursday, August 30, 2007 1:04 PM