8.3 Importing XML into Access Using the GUI


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 box
figs/oxml_0814.gif


You 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 documents
figs/oxml_0815.gif


You 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 options
figs/oxml_0816.gif


If 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 data
figs/oxml_0817.gif


Access 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 there
figs/oxml_0818.gif


Using 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 attributes
figs/oxml_0819.gif


If 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 transformation
figs/oxml_0820.gif


When 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 import
figs/oxml_0821.gif


In 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 document
figs/oxml_0822.gif


While 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.



Office 2003 XML
Office 2003 XML
ISBN: 0596005385
EAN: 2147483647
Year: 2003
Pages: 135

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net