Forum Discussion

JBLT-77's avatar
JBLT-77
Iron Contributor
Jun 04, 2022

Problems Importing XML Files Into Access

I'm trying to import several XML files into Microsoft access.  When I use the XML import wizard however, access imports the XML file creating multiple tables, rather than a single table where each record in the table is from one of the XML files.  Is there a way to import an XML file into a single table, rather than it creating multiple tables?

9 Replies

  • tsgiannis's avatar
    tsgiannis
    Iron Contributor
    If you want total control with XML you need to do it via code.
    Something like that
    Set xmlDoc = New MSXML2.DOMDocument

    intFile = FreeFile()
    Open strXMLFilename For Input As intFile
    strXMLFile = input$(LOF(1), 1)
    Close intFile

    Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
    xmlDoc.LoadXML strXMLFile
    Then you will iterate its nodes..grab the values and place them accordingly
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    JBLT-77 

     

    Please clarify what, exactly, you need to work with, one XML file or multiple XML files.

     

    "...access imports the XML file"

    "... each record in the table is from one of the XML files" 

    • JBLT-77's avatar
      JBLT-77
      Iron Contributor
      I have multiple xml files I need to import. They import the data into the same tables, but that’s the problem access thinks that the xml fields should be a table instead importing them as columns to a single table.
      • George_Hepworth's avatar
        George_Hepworth
        Silver Contributor

        JBLT-77 We are getting closer, thanks.

         

        Each XML file is supposed to import data into a table. There are different XML files, so there is one table for each XML file, correct? Or should all of the XML files import into the same Access table? That may not be relevant to the specific problem, but it's still not quite clear how it all needs to work.

         

        The problem point is that the import process creates one table for each field in the XML file. How are these XML files created? Have you independently validated that they are, in fact, well-formed XML? Is it possible that the problem is not in the import, but in the way the XML is structured? Can you open the XML files in an application like NotePad++ and verify that it is good?

         

        Is it possible to share a sample of one of these XML files? 

         

Resources