write XML file to database

M

mattsrebatespam

Member
Joined
Feb 15, 2005
Messages
10
I have an XML file that I would like to write to a database. I know how to load the XML file to a dataset by using the DataSet.ReadXml command but I do not know how to write the resulting dataset to the database. Any ideas?
I am using an MS Access database.
 
almost there...

I figured out how to update from the XML file but have a few issues. Here is the code I am using:

Code: 
Dim MyDataSet As New DataSet
MyDataSet.ReadXml("XMLFile.xml")
Dim MyOleDbDataAdapter As New OleDbDataAdapter("SELECT * FROM MyTable", OleDbConnection)
Dim MyOleDbCommandBuilder As New OleDb.OleDbCommandBuilder(MyOleDbDataAdapter)
MyOleDbDataAdapter.Update(DemographicDataSet, "MyTable")

I am having some issues with fields that are not present in every XML entry though. I have some fields in the MyTable database table that are not in every entry, they are in some though. I am getting errors from the program complaining about not finding the DataColumn in the DataTable for the SourceColumn. The fields in the database are not required.

Any ideas?
 
unfortunately it was not by me. Here is what some of it would look like:
<TABLENAME>
<FIELD1>123</FIELD1>
<FIELD2>123</FIELD2>
<FIELD3>123</FIELD3>​
</TABLENAME>
<TABLENAME>
<FIELD1>123</FIELD1>
<FIELD2>123</FIELD2>​
</TABLENAME>
<TABLENAME>
<FIELD1>123</FIELD1>
<FIELD2>123</FIELD2>
<FIELD3>123</FIELD3>
<FIELD4>123</FIELD4>​
</TABLENAME>​
My database table has all 4 fields defined in it. The xml file will load as long as there are the correct number of fields present in the file. It is horrid XML but it is what I have to work with :(
 
Since and XML file is just text, to fix it, you could use a streamreader and writer to read through the file line by line and add the missing elements as empty strings.

Then all of the fields would be there and reading the new XML file would not be a problem.

Another way would be to not use the XMLReader at all and instead use a stream reader, read the file line by line and pickout the data that is provided and add it manually to your dataset.

With the second method, you could insert the elements directly into your database without the use of a dataset.

Some others may have better ideas then I have, but as I always like to quote, junk in = junk out. And now you are stuck fixing someone elses junk.

Chester
 
Back
Top