How to convert XML data to Access

This HOWTO demonstrates how to easily convert XML to Microsoft Access using the Exult XML Conversion Wizard.

To demonstrate how to convert XML to Access, we will use the publicly available information about everybody who has served in the US Congress ( House of Representatives and US Senate), available here: people.xml.

  • A view of the sample data.

    XML Data on US Congress used as sample to demonstrate
       conversion of XML to Access.

  • Start up Exult and click Next on the opening screen.

    Opening Screen of Exult XML Conversion Wizard

  • Click "Add" to select the XML file to convert, navigate to the folder where the XML file is stored and open it. The screen shot below shows that the file of size 6.57MB has been selected for processing. Click Next when done.

    Selecting the XML file to convert to Access using the
       Exult XML Conversion Wizard

  • This screen shows the progress of parsing the XML file. For a large file, the processing could take some time. Exult has been specially optimized for converting large XML files without running into memory limitations. This is accomplished by processing parts of the XML file without loading it entirely into memory.

    To stop the processing at any time, click the Cancel button. Once the processing is complete, click Next.

    Converting the XML file to Access using the Exult XML
       Conversion Wizard: Importing the XML file into Exult.

  • We can now preview the imported XML data. The top panel shows the data tables identified from the XML and the bottom panel shows the data for the selected table. The table hierarchy shown in the top panel is derived from the nesting of XML tags and is built in a natural, easy-to-understand way.

    Each table shown in the top panel also shows the number of rows in the table. For this example, we can see that 12489 rows were found for the US Congress people. A sample cross-section of the data is shown below for each of the major tables: PEOPLE and ROLE. Once you are done previewing the data, select the tables required to be exported to Access and click Next.

    Previewing the imported XML data when converting to Access
       using the Exult XML Conversion Wizard Previewing the imported XML data when converting to Access
       using the Exult XML Conversion Wizard

  • The Output type screen is where we select how to export the data. We could choose to save the XML data to an Excel spreadsheet, CSV, HTML or Microsoft Access. Here we choose Microsoft Access and click Next.

    Saving the converted XML data to Microsoft Access using
       the Exult XML Conversion Wizard

  • Next step is to select where the Access database is to be saved. Click Browse to pick a location. Also select the version of Access database to be created: Access 2007, Access 2003 or Access 97. Access 2007 should be selected only if Access 2007 is actually installed on this computer since the export requires certain components that are installed only when Access 2007 is installed.

    Browse to and select the Access Database location to save
       the selected XML data using the Exult XML Conversion
       Wizard

  • Once the location of the Access database is selected, click Next to begin the output process. If the database is large, the process could take some time. Click cancel to abort the saving if it takes too long. Click Next once the process is finished.

    Saving the selected XML data to Microsoft Access could
       take some time depending on the size of the database.

  • And that is all there is to it converting XML to Access using the Exult XML Conversion Wizard. Click Finish to open the Access database in Access (if installed).

    Selecting the XML file to convert to Access using the
       Exult XML Conversion Wizard

  • Let us now use Access 2007 to view the converted XML data. Here we see a snipped of the data with the columns properly identified from XML. We also see the relationship between the parent and child rows that have been automatically exported from the XML structure.

    XML rows with parent-child relationships built from XML
       structure has been exported to Microsoft Access using the Exult
       XML Conversion Wizard

  • When we look at the relationships between the tables, we see that the relationship dictated by the XML structure has been automatically exported to Access. All this accomplished without a single line of code.

    Relationships dictated by XML structure has been mirrored
       in the Access database between parent and child tables using
       the Exult XML Conversion Wizard

That was a short and easy task converting XML to Access using the Exult XML Conversion Wizard. You can now download a free trial and check it out for yourself.

Argon DataViz

Create Pivot Tables and Pivot Charts from Large Datasets.

Swift XML Converter

Query & extract XML data into Microsoft Excel (XLS), Microsoft Access (MDB or ACCDB), or CSV.

Exult Standard

Convert XML into Microsoft Excel (XLS), Microsoft Access (MDB or ACCDB), or CSV.

Exult SQL Server

Shred and import XML into SQL Server.

Exult MySQL

Import XML data into automatically created MySQL tables.

Exult Oracle

Import XML into Oracle.