Importing XML Files into Access 2007 - Another method for bringing in an XML file
(Page 4 of 4 )
As previously noted in earlier tutorials the MS Excel program does a better job of importing an XML file even if the XML nodes contain attributes. The next picture shows how you may import the same webstudents.xml file using MS Excel 2007. Click on the From XML Data Import option to open or map to an XML file.
This displays the following Excel message as shown here.
The Excel program is going to create a schema of the source when you click the OK button, which displays the following window, named ImportData. Here you have the option of placing your file in the existing worksheet or in a new worksheet. It also offers a default location beginning with the first cell in the current work sheet.
When you accept the default and click on OK, it brings the data into the work sheet as shown in the next window.
This sheet can be imported into Access 2007 with ease as shown in the next two windows. Here you are bringing Excel file data into Access 2007 by choosing the appropriate icon in the External Data tab.
This opens the following window, Get External Data - Excel Spreadsheet, where you will browse to the file saved in the step when you imported the XML file into MS Excel 2007.
After browsing to the saved worksheet file when you click OK, you will display the following window, which shows the Import Spreadsheet Wizard's first step.
When you click on the next button you will pop open the following window where you indicate that the first row has column headings.
Place a check mark to indicate that the First Row Contains Column Headings and click on the Next button. The following window gets displayed. Choosing No Duplicates for the ID will allow it to make this column the Primary Key later. You alter the properties of each of the columns one by one. In the window shown click on the Next button.
In this step you can choose the Primary key as shown in the next window. By default Access 2007 chooses the Primary key as a surrogate key.
When you click on the Next button you will be asked for name for the table. Access 2007 provides a default but here webstudents is indicated as shown.
When you click Finish it takes you back to the Save Import steps window. When you click OK it will create a table, webstudents, whose design is shown in the next window.
Summary
Importing XML files having nodes with attributes does not work, a behavior similar to that experienced with Access 2003. However, if the XML file has repeated nodes like the ones returned by a database, import is effortless. MS Excel can handle XML files with nodes having attributes.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 4 
