Custom Project Importing XML Data to an Access Database and Applying a Transform

Part 1: Create a Custom Transformation File to be Used After the XML Data Import

1. Open Notepad and enter the statements as shown below. <?xml version="L0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="html" version="4.0" indent="yes"/>

<xsl:template match="dataroot"> <html> <body>

<xsl:apply-templates select="Customers"/> </table> <table>

<xsl:apply-templates select="Customers/Orders"/> </table> </body> </html> </xsl:template>

<xsl:template match="Customers">

Part V

<Customer>

<CustomerID>

<xsl:value-of select="CustomerID"/> </CustomerID> <CompanyName>

<xsl:value-of select="CompanyName"/> </CompanyName> </Customer> </xsl:template>

<xsl:template match="Customers/Orders"> <Order>

<OrderID>

<xsl:value-of select="OrderID"/> </OrderID> <OrderDate>

<xsl:value-of select="substring(OrderDate, 1, 10)"/> </OrderDate> <ShippedDate>

<xsl:value-of select="substring(ShippedDate, 1, 10)"/> </ShippedDate> <RequiredDate>

<xsl:value-of select="substring(RequiredDate, 1, 10)"/> </RequiredDate> <Freight>

<xsl:value-of select="format-number(Freight,'####0.00')"/> </Freight> </Order> </xsl:template>

</xsl:stylesheet>

2. Save the file as CustomerOrders.xsl in the Learn_XML folder. You must type the file extension to ensure that the file is not saved as text.

3. Close Notepad.

Since you've already created a similar stylesheet in Hands-On 29-5, the contents of the CustomerOrders.xsl file should be recognizable. All that's different here are the <Customer> and <Order> tags that specify the names of Access tables where we want to place our XML data. When importing data, tables are named according to the name of the XML element being imported. If the Access database already has a table with the specified name, a number is appended to the name.

Part 2: Export the Customers Table and the Related Orders Table to an XML File

1. In the Northwind Database window, highlight the Customers table and choose File | Export.

2. In the Export Table 'Customers' To window perform the following:

Select the Learn_XML folder in the Save in drop-down list. ■ Enter CustomerOrders in the File name box.

Taking Your VBA Programming Skills to the Web

■ Select XML from the Save as type drop-down box.

3. In the Export XML dialog box, the first two check boxes should be selected. Click the More Options button.

4. In the Data to Export area of the Export XML dialog, select the check box next to the Orders table. Both Customers and Orders tables should be selected.

5. Click OK to perform the export of all the records in the selected tables.

Part 3: Import to an Access Database Only Two Columns from the

Customers Table and Five Columns from the Orders Table

1. Open the Acc2003_Chap29.mdb database file that you created in Hands-On 29-7.

2. In the Database window, choose File | Get External Data | Import.

3. In the Import window, select the CustomerOrders.xml file in the Learn_XML folder and click the Import button.

Access displays the Import XML window with the file's Customers and Orders tables listed. By expanding nodes in the tree structure you can see the columns in each table, but you cannot indicate which columns to import, as Access always imports the entire file by default. You can, however, tell Access to perform a custom XSLT transform to import only the columns needed.

4. In the Import XML window, click the Options button.

5. Click the Transform button.

6. In the Import Transforms window that appears, click the Add button to apply a transform before importing.

7. Access displays the Add New Transform window. Switch to the Learn_XML folder and select the CustomerOrders.xsl file that you created in Part 1 of this project. Click the Add button to add this file to the list of transforms.

8. In the Import Transforms window, click OK.

9. Back in the Import XML window make sure that the Structure and Data option button is selected under Import Options and click OK. Access imports the data and displays a message when the process is completed.

10. Click OK to clear the confirmation message.

11. In the Database window notice the appearance of two new tables: Customer and Order. Open both tables and check their contents.

As you can see, Access has applied the custom stylesheet before importing the data and only the columns specified in the stylesheet were imported (Figure 29-16).

Part V

Part V

Figure 29-16: Applying a custom transformation file before XML data import limits the data imported to an Access database.

12. Open the Order table in Design view. Notice that all the fields in this table have been assigned the Text data type. After importing data or table structure you can change the data type of fields.

13. Change the data type of the OrderDate, ShippedDate, and RequiredDate columns to Date/Time and the Freight column's data type to Currency to match the original Orders table.

14. Save the modified Order table.

15. Close the Acc2003_Chap29.mdb database file.

0 0

Post a comment