12.6. Importing XML data It is also possible to import XML data into an Access database. This is a useful way to populate an Access database initially, or to add new data incrementally over time. 12.6.1 A basic import First, let's perform a basic import of an entire set of tables. To do this: - 1. Start with an empty, newly-created database.
- 2. On the File menu, point to Get External Data, then click Import.
- 3. In the Files of type list, select XML (*.xml;*.xsd).
- 4. Select the XML file containing the data you wish to import. For our example, use ORDERS all tables.XML.
- 5. Click Import.
- 6. This displays the Import XML dialog shown in Figure 12-14.
Figure 12-14. The Import XML dialog 
The large pane in the Import XML dialog shows the structure of the data to be imported. This is for informational purposes only; selecting table or column names has no effect on the import. At the bottom of the dialog (after you have clicked Options), you have three Import Options: -
Structure Only will create a table but will not populate it with data. -
Structure and Data (the default) will create a table and populate it with data. If a table by that name already exists, it will create a new table by appending a number to the name. For example, if the ORDERS table already exists, it will create ORDERS1. -
Append Data to Existing Table(s) will add the data to an existing table with the same name. If it cannot find a table with the appropriate name, it will create it. For this first example, click Structure and Data, then OK. You will see that it creates all three tables and populates them with all the existing rows. The designs of the tables match the design of the tables in the order.mdb database. This is because the schema that is related to the ORDERS all tables.xml file, ORDERS all tables.xsd, contains information about the keys and column datatypes of the table. If the schema were not present, the import would still have created the tables, but it would not have defined keys for the tables, and it would have given every column the type Text. 12.6.2 Appending data If you are interfacing your Access database with an e-commerce system, you may want to append data to existing tables rather than create new ones. For example, if a new order is received from a customer, you want to add it to the orders database. This can be accomplished checking the Append Data to Existing Table(s) box in the Import XML dialog. The import process will generate errors for any records that have duplicate keys. For example, if your order contains products information that is already in the database, Access will raise an error message saying that it could not insert the row because of a duplicate key. It will not update the product row with the new information from the XML document. 12.6.3 Creating your own XML document for import So far we have assumed that you already had a valid import file handy. However, you may be trying to map an existing XML document to Access, or creating one from scratch. In this case, it is helpful to understand how Access maps XML elements to tables and columns. Access makes certain assumptions about the way the XML document is organized, namely that: -
The dataroot element contains all of the data. -
Any elements (other than the root element) that have child elements are assumed to represent rows in tables whose name is the same as the row's element-type name. For example, in Example 12-1, since ORDERS elements have children, Access assumes that it should create or use a table named ORDERS, and insert a row in this table for every ORDERS element in the XML document. -
The children of these row elements are assumed to represent columns. For example, in Example 12-1, Access creates columns for all the children of ORDERS, such as DATE and CITY. Access will simply ignore elements that do not fit into this paradigm, such as elements that are children of the root element and do not have children of their own. Access will also ignore any data in attribute values. Tip  | There is a fairly easy way to determine how your XML document needs to be structured for import into Access. You can export an example from the database using the Export process described earlier in this chapter. You can then use the generated data file as an example, and use the generated schema to validate any incoming documents. |
12.6.4 Applying a transform on import The XML document you want to import will not always be structured as described in the previous section. You may have different element-type names in your XML document, or an entirely different structure from the one to be used in the database. For example, suppose we were starting with the other kind of order document that was shown in Example 12-5, and we wanted to import it into our ORDERS database. We would need a transformation to change the element-type names and structure to match the Access tables. A simple XSLT stylesheet that accomplishes this transformation is shown in Example 12-7. Example 12-7. Transforming the order on import (order transform for import.xml) <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:ord="http://xmlinoffice.com/orders"> <xsl:template match="/ord:order"> <dataroot xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <ORDERS> <!--assumes only 1 order in the source document--> <ORD_NUM><xsl:value-of select="@number"/></ORD_NUM> <DATE><xsl:value-of select="ord:date"/></DATE> <CUST_NAME><xsl:value-of select="ord:customer"/></CUST_NAME> <ADDRESS><xsl:value-of select="ord:shipTo/ord:addr"/></ADDRESS> <CITY><xsl:value-of select="ord:shipTo/ord:city"/></CITY> <STATE><xsl:value-of select="ord:shipTo/ord:state"/></STATE> <ZIP><xsl:value-of select="ord:shipTo/ord:postal_code"/></ZIP> <xsl:apply-templates select="ord:items/ord:item"/> </ORDERS> </dataroot> </xsl:template> <xsl:template match="item"> <ORDER_ITEMS> <ORDER_NUM><xsl:value-of select="/ord:order/@number"/></ORDER_NUM> <PROD_NUM><xsl:value-of select="@number"/></PROD_NUM> <QUANTITY><xsl:value-of select="ord:quant"/></QUANTITY> <SIZE><xsl:value-of select="ord:size"/></SIZE> </ORDER_ITEMS> </xsl:template> </xsl:stylesheet>
To specify that a transform should take place on import: - From the Import XML dialog, click Transform. This brings up the Import Transforms dialog shown in Figure 12-15.
Figure 12-15. The Import Transforms dialog 
- Add a transform by clicking Add, browsing to the XSLT file name, and clicking Add. In our example, use order transform for import.xsl.
- Select the transform you want to use, and click OK to apply the transform.
The Import Transforms dialog lists all the available transforms. It is possible to add more than one transform to the list. However, only one will be applied on any given import: the one that is selected when the OK button is clicked. If the transform generates an XML document that cannot be imported into Access, you will receive an appropriate error message. You will then be asked whether you want to save a copy of the transformed file to a temporary directory. It is useful to do this, since examining the transformed file often makes any errors in the stylesheet obvious. |
