How to import an XML file into MS Access 2007

Posted on 2012-09-09
Last Modified: 2012-09-28
I am trying to import an xml file  into MS Access

When I manually import the file using Structure and Data it imports fine, but creates several tables within MS Access: Cell, DocProperties, ExcelWorkbook,Pane,Style,Table, and Worksheet Options.  I find all of my data in the table called Cell and with a column called Data.

When I try to import the file into MS Access using the below code it only works if the 7 tables above still exist.  Am I always forced to load this file into a table called Cell ?  Why do I need to maintain all of these 7 tables in order to do the import from the XML file into MS Access ?  

Is there a better way of importing an XML file int MS Access than I have provided ?  

Const acAppendData = 2
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Scripts\Test.mdb"
objAccess.ImportXML "c:\scripts\test.xml", acAppendData

Open in new window

Question by:upobDaPlaya
    LVL 84

    Accepted Solution

    ImportXML is fine for many things, but if you need tight control over exactly what happens with the XML file you'll need to use the XML libraries to move the data into Access.

    There's a sample bit of coding in this thread:

    Basically you open the XML file and "walk" the elements, and figure out what to do with each one (i.e. import into Table1, for example, or update an existing record in Table2).

    Try the sample above, and post back here with questions.
    LVL 44

    Assisted Solution

    Part of the reason the tables have to exist is that you are specifying acAppendData to the ImportXML method.

    Can you post a reasonably small sample of the XML? (<50k)

    One possibility is to create an XSLT that produces a version of the XML with only the elements you need.  This is similar to the LSMConsulting suggestion, differing in the location of the code.

    Author Comment

    Thanks for the responses..I am out of town for a few days so on Sunday I will give this a try and post back...I want some time to really review the code and make sure I understand..I apologize in advance for not be able to close out the questions sooner as protocol may dictate..Many thanks again for your feedback...

    Author Comment

    I am working with the sample code provided by LMS and I think this will work well..I debated about programatically opening the file in excel and saving it as an xlsx file, but the code provided is more straight-thru...

    Although one thing I can not figure out is what .nextnode is doing in the code ?
    LVL 84
    .NextNode "gets" the next node in the XML file. It's one of several ways you can "walk" the XML file.

    Author Closing Comment

    Very cool..thanks

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now