Reading XML file back into a database

  • 19 years ago

    What's the best way of doing this?  
    The XML file is written using the ADO recordset Save method and can obviously be read back in using the Open method.


    Now picture this.  You have 2 databases of the same structure and compatible data types.  One on SQL server and one in Access.  You create the XML file from the Access database, and want to put it into the SQL server.  When you open the XML file using the open method how do you then write that recordset to the database.  would you have to loop thorugh the records and insert or is there a sort of 'batch insert' way of doing it?


    and I guess ... is XML the best way of doing this?  The system I'm working on currently has two options.  write the recordset into a separate, smaller database then just read it record by record back into the SQL server.  The other is write it to a formated, non delimited text file that then gets read, line by line into the database.  


    I'm just wanting a faster method of processing this.  SQL packages from the source to the destination is not an option as the source would be at a client site and the SQL server is on site here.

  • 19 years ago

    Hi!


    XML might be a solution, but I'd personally prefer using a textfile. This at least guarantees that both the export from Access and the import into SQL will proceed without any mistakes... I already had some nice trouble exporting from Access...


    Somehow I cannot imagine anything like a batch import from an XML file. You'll have to read the thing line by line anyway, that's even the case importing Access straight into SQL...


    Which brings me to the next point: why not export data to some back-end database from within your client, then compact this back-end and send it straight to your site? Or am I forgetting something now?


    Regards,


    Thamar.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“XML is like violence - if it's not working for you, you're not using enough of it.”