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,
Chris
ctownsen80Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff DarlingDeveloper AnalystCommented:
There are a 3 main parts to this.

1. Get list of XML Files

2. Perform XSL transform to get Needed table

3. Import Needed Table into Access


VBScript
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
strCurrentDirectory = "E:\today\01\XMLData\"

Set objFolder = objFS.GetFolder(strCurrentDirectory)
Set colFiles = objFolder.Files

For Each objFile In colFiles
      WScript.Echo "Processing " & objFile.Name
      Call ImportXMLFile(objFile.Name)
Next


Public Sub ImportXMLFile(strFilename)

Dim txtXMLFile,txtXMLNEEDFile

txtXMLFile = strFilename
txtXMLNEEDFile = Mid(strFilename,Len(strFilename)-4)&"NEED.xml"


' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' Perform XSLT to Extract Needed Table
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Dim xmlDocument, xmlStylesheet,xmlTemp,xmlElement
Set xmlDocument = CreateObject("Msxml2.DOMDocument")
Set xmlDocumentCopy = CreateObject("Msxml2.DOMDocument")
xmlDocument.async = False
xmlDocument.load "E:\today\01\XMLData\" & txtXMLFile

Set xmlStylesheet = CreateObject("Msxml2.DOMDocument")
xmlStylesheet.async = False
xmlStylesheet.load "E:\today\01\needed.xsl"

xmlDocument.createProcessingInstruction "xml", "version=""1.0"" encoding=""utf-8"""

StrXML = xmlDocument.transformNode(xmlStylesheet)

xmlDocumentCopy.loadXML strXML

if (xmlDocumentCopy.firstChild.nodeType = 7) and (xmlDocumentCopy.firstChild.nodeName = "xml") then
    set child = xmlDocumentCopy.firstChild
    xmlDocumentCopy.removeChild child
end if

set xmlTemp = xmlDocumentCopy.createProcessingInstruction("xml","version='1.0' encoding='utf-8'")
xmlDocumentCopy.insertBefore xmlTemp, xmlDocumentCopy.firstChild

xmlDocumentCopy.save "E:\today\01\XMLData\Temp\" & txtXMLNEEDFile

Set xmlDocument = Nothing
Set xmlStylesheet = Nothing

' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' Import Needed table
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Const acAppendData = 2

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase ("E:\today\01\MDBData\MyDataBase.accdb")

objAccess.Application.ImportXML ("E:\today\01\XMLData\Temp\" & txtXMLNEEDFile), acAppendData

End sub

Open in new window


Sample XSL

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
    <root><Table>
    <xsl:for-each select="root/Needed/item">
	<Extracted_Data>
          <ID><xsl:value-of select="ID"/></ID>
         <Name><xsl:value-of select="Name"/></Name>
		 </Extracted_Data>
    </xsl:for-each>
</Table>
</root>
</xsl:template>
</xsl:stylesheet>

Open in new window


Sample XML

<?xml version="1.0" encoding="utf-8" ?>
<root>
<TableA>
    <item>
        <ID>001</ID>
        <Name>John Smith</Name>
    </item>
    <item>
        <ID>002</ID>
        <Name>Ben Jammin</Name>
    </item>
    <item>
        <ID>003</ID>
        <Name>Phil Dozer</Name>
    </item>
</TableA>
<Needed>
    <item>
        <ID>001</ID>
        <Name>Jeff FindSteen</Name>
		</item>
    <item>
        <ID>002</ID>
        <Name>Lara Willard</Name>
    </item>
    <item>
        <ID>003</ID>
        <Name>Adam West</Name>
    </item>
</Needed>
<TableB>
    <item>
        <ID>001</ID>
        <Name>Gulab Chadio</Name>
    </item>
    <item>
        <ID>002</ID>
        <Name>Lara Dita</Name>
    </item>
    <item>
        <ID>003</ID>
        <Name>kabutar west</Name>
    </item>
</TableB>
</root>

Open in new window


Sample Transformed XML

<?xml version="1.0" encoding="utf-8"?>
<root>
	<Table>
		<Extracted_Data>
			<ID>001</ID>
			<Name>Jeff FindSteen</Name>
		</Extracted_Data>
		<Extracted_Data>
			<ID>002</ID>
			<Name>Lara Willard</Name>
		</Extracted_Data>
		<Extracted_Data>
			<ID>003</ID>
			<Name>Adam West</Name>
		</Extracted_Data>
	</Table>
</root>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access has an ImportXML method:

https://msdn.microsoft.com/en-us/library/office/ff823157(v=office.14).aspx

The XML has to be properly formed, and if it includes relational data then it must be really well-formed.
0
Jeff DarlingDeveloper AnalystCommented:
If you have a sample XML and XSD that would be helpful.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ctownsen80Author Commented:
@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.

Chris
0
Jeff DarlingDeveloper AnalystCommented:
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.
0
Jeff DarlingDeveloper AnalystCommented:
some of the locations are hard coded.  I have them pointing E:\today\01\ path, but that you can change that.
MyDataBase.accdb
0
ctownsen80Author Commented:
@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.
0
Jeff DarlingDeveloper AnalystCommented:
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.
0
ctownsen80Author Commented:
Got it! I had to enlist the help of a co-worker, but together using your code, we got it handled. Thanks Jeff!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.