Hussein-Ayres
New Member
I need a fastest solution for transferring data from XML file to MySQL tables. I have XML file with multiple tables inside, like this:\[code\]<?xml version="1.0" standalone="yes"?><RawData xmlns=""> <Table1> <ID_Table1>1</ID_Table1> <Name>Victor</Name> </Table1> <Table2> <ID_Table2>1</ID_Table2> <Quantity>10</Quantity> </Table2></RawData>\[/code\]and within VS2010, i have DataSource with DataTables and TableAdapters from MySql db for Table1 and Table2.My goal was to read XML file and pass its data directly to these DataTables with this:\[code\]myDSDataSet eDS = (myDSDataSet)this.FindResource("myDS"); // Declared in XAMLOpenFileDialog dlg = new OpenFileDialog();dlg.Filter = "XML Files|*.xml";dlg.Title = "Select a XML File";Nullable<bool> result = dlg.ShowDialog();if (result == true){ mt1TableAdapter mt1_TA = new mt1TableAdapter(); mt2TableAdapter mt2_TA = new mt2TableAdapter(); manager = new TableAdapterManager(); xmlDS = new DataSet(); dt = null; try { xmlDS.ReadXml(dlg.FileName,XmlReadMode.InferTypedSchema); for (int i = 0; i < xmlDS.Tables.Count; i++) { dt = xmlDS.Tables.Copy(); eDS.Tables[eDS.Tables.IndexOf(xmlDS.Tables.TableName)].Merge(dt); } mt1_TA.Update(eDS.mt1); mt2_TA.Update(eDS.mt2); MessageBox.Show("Loading complete."); } catch (Exception error) { MessageBox.Show("ERROR: " + error.Message); } }\[/code\]But after executing this code i got 2 big problems:1. If data types differ i get exception (in DataSource field is DateTime, XML field is read as string)2. Calling TableAdapter.Update() takes a long time to save data to db (15k rows takes 10-15mins)So... my question is, Can someone please help me solve these two problems or give me some direction as to what is the fastest and best method for saving XML data to mysql.Note: - I'm using VS2010 and MySQL 5.1. - XML file needs to be loaded from external source. - XML has its xmlns but its omitted here for simplicity. - I tried upgrading MySQL to 5.6 and using LOAD XML but i cant use this command within stored procedure.Thanks