Avatar of ctownsen80
Flag for Afghanistan asked on

Import XML FIles into Access Table

I have several xml files (100's) in a folder.

The location of the files is C:/My Documents/The_Files/.  
The location of the database to where the data needs to be imported is C:/My Documents/The_Database/.

I would like to be able to import these XML files into an access table called "Extracted_Data".  I have research various blogs and potential solutions on here, but I cannot seem to apply any to my needed solution. I am familiar with VB but far from an expert.  I am working with MS Access 2010.

When I perform the import manually, there are several tables that are imported into the database, but the main table with all pieces of information I need is called "Needed".

Would anyone be able to script a code that could upon execution, import all of these xml files onto the one single table in access? Is there any additional information needed from me to help?

Thanks in advance,
XMLMicrosoft AccessVB ScriptVBA

Avatar of undefined
Last Comment

8/22/2022 - Mon
Jeff Darling

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott McDaniel (EE MVE )

Access has an ImportXML method:


The XML has to be properly formed, and if it includes relational data then it must be really well-formed.
Jeff Darling

If you have a sample XML and XSD that would be helpful.

@Jeff Darling -- Hi Jeff. Thank you very much for working on this. Do I need to enable any selections from the reference library in order for the code to work? When I try it right now, I just get a prompt "Macros" for me to select another macro.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeff Darling

This is a VBScript, not VBA.   I just now noticed that you specified both VBA and VBScript.  (VBA in tags and VBScript in Topics.)

I'll look into a VBA sample.
Jeff Darling

some of the locations are hard coded.  I have them pointing E:\today\01\ path, but that you can change that.

@Jeff Darling --- My bad, I'm sorry about that. Shows my VBA ignorance. I change all of the locations, based on the one in your sample database. I got following error message:

Run-Time Error '-2147467259(80004005)':
The stylesheet does not contain a document element. The stylesheet may be empty, or it may not be a well-formed XML Document.

And this portion of the script was highlighted:
"strXML = xmlDocument.transformNode(xmlStylesheet)"

Would it help if I can provide a sample file im working with? I would have to change a few things -- so ill keep my job :) .... but I would do that if it would make the sample more easy to develop.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeff Darling

Yes, that XSL file would definitely need to be modified to match up to your XML.  The purpose of that XSL file is to transform the input XML to isolate the Needed Table and to create the Isolated XML table in a format that the Access ImportXML function can understand.

If you have a sample XML without any sensitive info, I should be able to create the new XSL for you.  I'm assuming that all the XML files have the same schema.  If not, then this project would be much more complicated.

Got it! I had to enlist the help of a co-worker, but together using your code, we got it handled. Thanks Jeff!