Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Use SQL to query XML files from Excel (Read 5783 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Use SQL to query XML files from Excel
24.03.06 at 15:51:15
 
Richard from Canada asked the following question.
 
Question:
I noticed that SQL*XL can publish data to XML.  My question is, can it read data from XML?
 
Here is our scenario.
 
We currently have a part of our application that publishes Exams.  These Exams are saved as XML files.  From these XML files, we can very easily extract the necessary information required by the spreadsheets into another XML file.  So, it would be ideal to populate the initial data from XML into the Spreadsheets.
 
Next, the users will populate / enter data into pre-defined columns in that spreadsheet.  We then want to post the resulting data (based on a button that runs a macro I suppose) directly to our database table.
 
So, the incoming data would be from an XML source.  But the outgoing / publishing data would go to our database.
 
I imagine it would be simpler to write macros to call the SQL*XL api to handle these based on two set events: a button to load data into Excel from XML, and a second button to save data from Excel to the database.
 
Answer:
Yes, SQL*XL can read from XML files. There is a driver to connect to XML files and then you can use the XML files as a database. References:
http://www.oraxcel.com/projects/sqlxl/help/techniques/connecting/xml_oledb.html
http://www.oraxcel.com/projects/sqlxl/help/techniques/data_link_properties/index .html#dl_viksoe.dk_xml
 
You can use SQL*XL to connect to your XML files from Excel and run a query to return the information into the spreadsheet. This would give you your basic table information. Your users will then add data to the spreadsheet as you indicate.  
The second part of the process can be handled by the SQL*XL insert multiple rows feature.
References:
http://www.oraxcel.com/projects/sqlxl/help/techniques/bulk_insert.htm
http://www.oraxcel.com/projects/sqlxl/help/dialogs/insert_multi/index.html
 
Finally, these two steps can be automated through macro recording. I suggest you record two macros initially:

  • Macro 1: load XML data into Excel
    Start the macro recorder, connect to the XML file, run the query, put the results in the spreadsheet, disconnect from the database. Stop the macro recorder.
  • Macro 2: insert the Excel data into the database
    Start the macro recorder again, connect to the database, run the insert multiple rows procedure, disconnect from the database and stop the macro recorder.
     
    With the basic macros you have now created it should be relatively straightforward to make the full application. If you have problems there I would be happy to help but it would be more appropriate to discuss these in the VBA forum. In short you need buttons that start the macros, cater for different numbers of rows from the XML data.
Back to top
 
« Last Edit: 24.03.06 at 16:00:40 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1