Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 18.1 Import XML Structured as Elements

18.1.1 Problem

You need to import simple XML data into a new table.

18.1.2 Solution

You can import XML into a new table from the File menu when the Tables category is selected in the Objects pane by following these steps:

Open 18-01.MDB.
Choose File Get External Data Import to bring up the Import dialog box.
In the Files of type drop-down list at the bottom of the dialog box, select XML (*.xml, *.xsd).
In the File name dialog box, navigate to the XML file you want to import, and click Import, which will load the XML Import dialog box. The samples include an XML file named 18-01.XML that you can use.
Expand the plus sign (+) to show the structure of the XML file and click the Options button to expand the dialog box, as shown in Figure 18-2.

Figure 18-2. Viewing the structure of an XML file when importing into Access

The Options button shown in Figure 18-2 enables you to specify how you'd like the XML imported. If you choose Structure and Data as shown here, a new table will be created. Click OK twice to confirm the import.

A new table named Car has been created.

18.1.3 Discussion

When importing an XML file that has the structure Access expects, containing a hierarchical set of nested elements, the table name is derived from the first element after the root, in this case Car. The Make, Model and Price elements become columns in the table. The source XML file looks like this:

<?xml version="1.0" encoding="UTF-8" ?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">
  <Car>
    <Make>Mini Cooper</Make>
    <Model>S</Model>
    <Price>20,000</Price>
  </Car>
  <Car>
    <Make>Lexus</Make>
    <Model>LS430</Model>
    <Price>60,000</Price>
  </Car>
  <Car>
    <Make>Porsche</Make>
    <Model>Boxter</Model>
    <Price>43,000</Price>
  </Car>
  <Car>
    <Make>Ford</Make>
    <Model>Mustang</Model>
    <Price>25,000</Price>
  </Car>
  <Car>
    <Make>Toyota</Make>
    <Model>Camry</Model>
    <Price>20,000</Price>
  </Car>
</dataroot>

When you open the Car table in Datasheet view, it looks like Figure 18-3, with the data organized by column (element), with each row representing a single Car element in the XML file.

Figure 18-3. The Car table in Datasheet view

If you import the same file a second time, choosing the same options, then a second table named Car1 will be created. Rows will not be appended to the first Car table unless you explicitly select that option.

18.1.4 See Also

The following MSDN article gives a good explanation of XML namespaces: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexxml/html/xml05202002.asp?frame=true

[ Team LiB ]