- XML: Like a Database
- The Crafty Database
- Exporting Information from an Access Database to a File
- Importing XML Information into an Access Database
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Importing XML Information into an Access Database
After customers have placed orders for crafts on a web site, we can upload the order information to the Crafty database via an XML document returned from the site. This strategy enables the crafter to maintain centralized records locally.
Suppose the order entry web site outputs the following XML file (called order.xml), complete with the included order data:
<?xml version="1.0" encoding="UTF-8" ?> - <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="order.xsd" generated="2004-02-28T05:52:25"> - <order> <OrderID>18</OrderID> <OrderDate>2004-04-01T00:00:00</OrderDate> <ShipName>John Jones</ShipName> <ShipAddress>1234 any street</ShipAddress> <ShipCity>your city</ShipCity> <ShipState>pa</ShipState> <ShipPostalCode>121211234</ShipPostalCode> <ShipPhoneNumber>8005551212</ShipPhoneNumber> <ShipDate>2004-04-13T00:00:00</ShipDate> <Product_x0020_Name>seaside cottage</Product_x0020_Name> <Quantity>1</Quantity> <UnitPrice>75</UnitPrice> <SalesTax>6</SalesTax> </order> - <order> <OrderID>25</OrderID> <OrderDate>2004-12-24T00:00:00</OrderDate> <ShipName>Jackie Jones</ShipName> <ShipAddress>345 Blue Road</ShipAddress> <ShipCity>Boston</ShipCity> <ShipState>Oh</ShipState> <ShipPostalCode>999999999</ShipPostalCode> <ShipPhoneNumber>8885551212</ShipPhoneNumber> <ShipDate>2004-12-29T00:00:00</ShipDate> <Product_x0020_Name>flower pot candle 3"</Product_x0020_Name> <Quantity>15</Quantity> <UnitPrice>2.75</UnitPrice> <SalesTax>6</SalesTax> </order> - <order> <OrderID>23</OrderID> <OrderDate>2004-03-11T00:00:00</OrderDate> <ShipName>Tom Jones</ShipName> <ShipAddress>134 main</ShipAddress> <ShipCity>pittsburgh</ShipCity> <ShipState>tx</ShipState> <ShipPostalCode>767671234</ShipPostalCode> <ShipPhoneNumber>8665551212</ShipPhoneNumber> <ShipDate>2004-03-13T00:00:00</ShipDate> <Product_x0020_Name>flower pot candle 1</Product_x0020_Name> <Quantity>9</Quantity> <UnitPrice>2.75</UnitPrice> <SalesTax>5.5</SalesTax> </order> </dataroot>
We can simply import this data into an existing Access database, uploading into an existing table, a new table, or even a new database.
To import the data, choose File, Get External Data, Import (see Figure 11). Select XML in the Files of Type list.
)
Figure
11 Importing an XML file.
Access infers the target table name from the following line in the file we're importing (see Figure 12):
xsi:noNamespaceSchemaLocation="order.xsd"
)
Figure
12 Specifying the target table.
You can import all of the information from the XML file, or select which inferred columns are imported into the database (see Figure 13).
)
Figure
13 Selecting the columns to import.
If you import the data without specifying any options, the data will be loaded into a new table in the database (see Figure 14). This technique allows you to examine the information to determine its legitimacy, accuracy, and ability to fit in with the other data in the table. For example, you may prefer state abbreviations, shipping addresses, and names to have appropriate capitalization; if so, you could opt to import the data into a new table and make your own transformations on the data to keep your base tables clean.
)
Figure
14 The new table resulting from a basic import operation.
Figure 15 shows the result you get if you opt to simply append the data to an existing table; information in a field is imported into the corresponding columns.
)
Figure
15 Appended data.