Access provides fewer options for importing XML, but what it provides is simple and reasonably solid. Access lets you import data that looks roughly like the data it exports, and only as tables or additions to tables. This can be a great way to load new data into a database or add newly updated information, but it does make it difficult to transfer complex interrelated structures between databases. A single document may contain XML that refers to multiple tables, of course, and XSLT transformations on import can help as well. To get started, we'll import the code shown in Example 8-11 into the Access database previously used for exporting. Example 8-11. New data for import<?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="ch0802.xsd"> <books> <ISBN>0596002637</ISBN> <Title>Practical RDF</Title> <Tagline>Solving Problems with the Resource Description Framework</Tagline> <Short_x0020_Description>The Resource Description Framework (RDF) is a structure for describing and interchanging metadata on the Web.</Short_x0020_Description> <Long_x0020_Description>The Resource Description Framework (RDF) is a structure for describing and interchanging metadata on the Web - anything from library catalogs and worldwide directories to bioinformatics, Mozilla internal data structures, and knowledge bases for artificial intelligence projects.</Long_x0020_Description> <PriceUS>39.95</PriceUS> </books> <books> <ISBN>0596003838</ISBN> <Title>Content Syndication with RSS</Title> <Tagline>Sharing Headlines and Information Using XML</Tagline> <Short_x0020_Description>RSS is sprouting all over the Web, connecting weblogs and providing news feeds.</Short_x0020_Description> <Long_x0020_Description>RSS is sprouting all over the Web, connecting weblogs and providing news feeds. Originally developed by Netscape in 1999, RSS (which can stand for RDF Site Summary, Rich Site Summary, or Really Simple Syndication) is an XML-based format that allows Web developers to create a data feed that supplies headlines, links, and article summaries from a web site</Long_x0020_Description> <PriceUS>29.95</PriceUS> </books> <books> <ISBN>0596002912</ISBN> <Title>XPath and XPointer</Title> <Tagline>Locating Content in XML Documents</Tagline> <Short_x0020_Description>Referring to specific information inside an XML document can be like looking for a needle in a haystack: how do you differentiate the information you need from everything else?</Short_x0020_Description> <Long_x0020_Description>Referring to specific information inside an XML document can be like looking for a needle in a haystack: how do you differentiate the information you need from everything else? XPath and XPointer are two closely related tools that play a key role in XML processing by allowing developers to find these needles and manipulate embedded information.</Long_x0020_Description> <PriceUS>24.95</PriceUS> </books> </dataroot> To get started, select "Get External Data" from the File menu, and select "Import . . . ." The dialog box shown in Figure 8-14 will appear. Figure 8-14. Initial import dialog boxYou may have to select XML from the "Files of type" drop-down menu at the bottom, as the dialog initially defaults to Access formats. Select the file ch0811.xml, and click Import. The Import XML dialog box shown in Figure 8-15 will appear. Figure 8-15. Import dialog box showing structure of XML documentsYou can click on the plus sign to the left of "books" if you want to inspect the structure. If you just click OK right now, Access will create a new table, books1 (or whatever number avoids a conflict), to import the XML into Access without conflicting with the prior XML table. That may be perfectly fine, as it gives you a chance to compare the new data with the old before merging the two. Access provides two more options, however: one that lets you just create a new table based on the structure of the XML file, and another that lets you append the data in the XML file to an existing table. In this case, we know the new books are different from the old books, so click on Options and select "Append Data to Existing Table(s)," as shown in Figure 8-16. Figure 8-16. Import dialog box showing more complex structure of XML documents, as well as append optionsIf you click OK now, the extra books will be added to the existing books table, as shown in Figure 8-17. Figure 8-17. The results of importing a document and appending its dataAccess refuses to import XML data that causes a conflict with existing key relationships. For example, if you import that same document again the same way, you'll be rewarded with the ImportErrors table shown in Figure 8-18. Figure 8-18. The results of importing a document and appending its data when the data is already thereUsing the Transform . . . button shown in Figure 8-16, you can also perform conversions that make it easier to import data that doesn't arrive in a form that meets Access' expectations. For example, suppose information about a new book arrived in the form shown in Example 8-12. Example 8-12. An attribute-based XML document for import<update> <books ISBN="0596003277" Title="Learning XSLT" Tagline="A Hands-On Introduction to XSLT and XPath" Short_x0020_Description="A gentle introduction to the complex intricacies of XSLT" Long_x0020 _Description="A gentle introduction to the complex intricacies of XSLT and XPath, walking through the spec from simple work to complex." PriceUS="34.95" /> </update> In Example 8-12, all of the data is stored in attributes, and Access won't even look at attributes during an import. To get this information into Access, you'll need to use a transformation, like the generic one shown in Example 8-13, which converts all attributes into child elements. Example 8-13. A stylesheet for transforming attributes into elements<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <!--Derived from recipe 6.1 of Sal Mangano's XSLT Cookbook--> <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/> <xsl:template match="@*"> <xsl:element name="{local-name(.)}" namespace="{namespace-uri(..)}"> <xsl:value-of select="."/> </xsl:element> </xsl:template> <xsl:template match="node( )"> <xsl:copy> <xsl:apply-templates select="@* | node( )"/> </xsl:copy> </xsl:template> </xsl:stylesheet> When applied to Example 8-12, the stylesheet in Example 8-13 will produce the result shown in Example 8-14, which Access can import easily. (Note that Access doesn't care what the name of the root element is; update is simply a useful description for human consumption.) Example 8-14. An elementized version of Example 8-12<?xml version="1.0" encoding="UTF-8"?> <update> <books> <ISBN>0596003277</ISBN> <Title>Learning XSLT</Title> <Tagline>A Hands-On Introduction to XSLT and XPath</Tagline> <Short_x0020_Description>A gentle introduction to the complex intricacies of XSLT</Short_ x0020_Description> <Long_x0020_Description>A gentle introduction to the complex intricacies of XSLT and XPath, walking through the spec from simple work to complex.</Long_x0020_Description> <PriceUS>34.95</PriceUS> </books> </update> If you tell Access to import ch0812.xml, the file shown in Example 8-12, you won't have much to choose from in the Import XML dialog box, as shown in Figure 8-19. Figure 8-19. Access' initial reaction to the document that stores data in attributesIf you click on Options and then on Transform . . . , you'll be able to add the stylesheet, much as you did for the export transformation. Add the stylesheet to the list of transformations and select ch0813, as shown in Figure 8-20. Figure 8-20. Selecting a stylesheet for transformationWhen you click OK, Access applies the transformation to the document, modifying the display of components you see, producing the result in Figure 8-21. Figure 8-21. A transformed document ready for importIn this case, the table already exists, so be sure to select "Append Data to Existing Table(s)." When you click OK, the data from Example 8-12 will be added to the table books, as shown in Figure 8-22. Figure 8-22. The result of importing a transformed documentWhile transformations work well for some kinds of import problems, they suffer from one major limitation: they have to be applied manually. The techniques for importing XML with Visual Basic for Applications, explored in the next section, do not support the use of stylesheets for transformation on import. |