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 FileGet 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

|
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