Question XML, Dataset and ADO.NET Question

[mikebrey]

I am trying to move data from one database to another via code. I can extract data from the source db (ibm UniVerse) to a xml document (no schema) and load it into a dataset. I am then trying to use that dataset and ADO.NET to load the data into the destination db (mssql 2008). The data comes out find and I can bind it to a datagridview for testing but when I try to update the mssql db I am getting table mapping errors on the dataset table. Does anyone have a suggestion on how to accomplish this? This is just a straight dump (insert) I am not worried about updating just yet.

Thanks.

 

[cjard]

To do this the easiest way, I would read the DW2 link in my signature, section Creating a Simple Data App

That will teach you how to connect you app to both the source db and the destiantion db, and you'll have a dataset for each in your project when youre done.. then just fill the source dataset, copy the relevant table data manually (row by row, table by table) using For loops, into the destination table, then send it into the destination db (use of the tableadapter.Update() method)

 

[mikebrey]

cjard, thanks for the reply. I was able to populate the dataset from a XML document created by an api to the multivalued db (ODBC does not work with reliability because of the vendor data structure). When I use data adapter to insert into the SQL database I am getting a column mismatch error. I tried to map the columns one to one but that did not seem to help. Do you have a generic example on how to map the dataset to SQL?

 

[cjard]

If you already have the source dataset filled youre halfway there. Use DW2 to learn how to link to SQL2008, and you'll end up witha datatable that represents your SQL Server table..

Now just iterate your source datatable, row by row, copying the data into the destination as you want.

Other options you could consider, are writing an XML transform to turn the XML universe puts out into something that will read into the SQL server dataset.. This is quite technically involved though and only a route I'd go if youre familiar with datasets and how they use xml read/write

 

[mikebrey]

sorry about the delay. I was sick for about two weeks and spent the time afterwards catching up and trying your suggestions. Unfortunately I an not able to use ODBC and will have to transform the xml for sql. Thank you again for help.

 

[MattP]

Use xsd.exe to make a schema.

Then follow this link Using SQLXML Bulk Load in the .NET Environment