8.4 Automating XML Import and Export

While the GUI is certainly the most flexible way to learn about Access' XML support, it can be tricky to explain if you're using XML to distribute information to users or collect information from them. Rather than tell users to go through a multi-step process, you can use the Visual Basic for Applications Application.ImportXML and Application.ExportXML methods to create buttons or other interfaces that let users get information in and out more easily.

Of the two methods, Application.ImportXML is by far the simpler. It only takes two argument: a data source most likely a file reference or a URL and an options constant. The choices for the options are acAppendData, acStructureAndData (the default), and acStructureOnly. These correspond to the behaviors described in Section 8.3.

For an example of how this might work, the XML in Example 8-15 is available at http://simonstl.com/ora/updateBook.xml.

Example 8-15. An online XML update file

<update> <books> <ISBN>0596003722</ISBN> <Title>XSLT Cookbook</Title> <Tagline>Solutions and Examples for XML and XSLT Developers</Tagline> <Short_x0020_Description>A comprehensive collection of recipes for applying XSLT in a  variety of situations.</Short_x0020_Description> <Long_x0020_Description>A comprehensive collection of recipes for applying XSLT in a  variety of situations, including structural changes, and conversion to XHTML, SVG, and  programming code.</Long_x0020_Description> <PriceUS>34.95</PriceUS> </books> </update>

To import this, create a form with a button on it, and add this code to the button:

Private Sub Command0_Click( )     Application.ImportXML "http://simonstl.com/ora/updateBook.xml", _         acAppendData End Sub

When you click on the button, which might look like Figure 8-23, your database will retrieve the XML from http://simonstl.com/ora/updateBook.xml and add its contents to the database.

Figure 8-23. A button for importing XML data from the Web

If your books table looked like Figure 8-22, it will now look like Figure 8-24.

Figure 8-24. The result of the automated importation of an XML document

As noted earlier, you can't apply a transformation when importing XML through Visual Basic for Applications, so the imported XML file must meet Access' structural expectations to start with. If you're providing XML specifically for the purpose of distributing it to Access databases, this shouldn't be a problem, but it may require some code, a temporary download, or some kind of proxy if your Access database has to import data that Access can't interpret automatically.

The Application.ExportXML method provides somewhat more control and functionality than its ImportXML companion, though it also lacks direct transformation capabilities. It takes eight arguments, listed here:

ObjectType

Most typically acExportTable, acExportQuery, or acExportReport, though you can also experiment with acExportForm, acExportFunction, acReport, acServerView, or acStoredProcedure.

DataSource

A string containing the name of the Access object typically the table or query you want to export.

DataTarget

The path to the XML document you want to export. Leave this blank if you're just exporting a schema.

SchemaTarget

The path to the XML Schema document you want to export. If you're just exporting data and not a schema, leave this blank.

PresentationTarget

The path to the XSLT that Access generates for creating an Internet Explorer interface to access data. Note that this is not a place for specifying an XSLT transformation.

ImageTarget

A path to a directory that will be used for exporting images if you're exporting a report.

Encoding

The encoding to use for the exported text files. This may be either acUTF16 (for UTF-16), or acUTF8 (for UTF-8). UTF-8 is the default.

OtherFlags

This field holds an integer that is the sum of several flags. Starting from a value of zero, add acEmbedSchema if you to embed a schema inside of the XML file instead of in a separate file. Add acExcludePrimaryKeyAndIndexes if you don't want the schema to contain index information. Add acLiveReportSource if this is to be connected to a Microsoft SQL Server database. Add acPersistReportML if you want to look at the ReportML Access uses internally. Add acRunFromServer if you want to create Active Server Pages (ASP) rather than HTML output for the PresentationTarget.

You can also specify additional objects to export as extra arguments after these, perhaps if you wanted to export multiple tables simultaneously. Most typically, you'll use just three arguments, as shown in this method:

Private Sub Command1_Click( )      Application.ExportXML acExportTable, "books", _           "C:\xml\booksExport.xml"    End Sub

When this code is used for the button shown in Figure 8-25, if you've done all the imports along the way, the results at C:\xml\booksExport.xml will look like Example 8-16.

Figure 8-25. A button for exporting XML to your hard drive

Example 8-16. The results of exporting all of the data in the books table using the button

<?xml version="1.0" encoding="UTF-8"?> <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2004-02-06T15:42:40"> <books> <ISBN>0596005385</ISBN> <Title>Office 2003 XML Essentials</Title> <Tagline>Integrating Office with the World</Tagline> <Short_x0020_Description>Microsoft has added enormous XML functionality to Word, Excel,  and Access, as well as a new application, Microsoft InfoPath.  This book gets readers  started in using those features.</Short_x0020_Description> <Long_x0020_Description>Microsoft has added enormous XML functionality to Word, Excel, and  Access, as well as a new application, Microsoft InfoPath.  This book gets readers started  in using those features.</Long_x0020_Description> <PriceUS>34.95</PriceUS> </books> <books> <ISBN>0596002920</ISBN> <Title>XML in a Nutshell, 2nd Edition</Title> <Tagline>A Desktop Quick Reference</Tagline> <Short_x0020_Description>This authoritative new edition of XML in a Nutshell provides  developers with a complete guide to the rapidly evolving XML space.</Short_x0020_ Description> <Long_x0020_Description>This authoritative new edition of XML in a Nutshell provides  developers with a complete guide to the rapidly evolving XML space. Serious users of XML  will find topics on just about everything they need, including fundamental syntax rules,  details of DTD and XML Schema creation, XSLT transformations, and APIs used for processing  XML documents.  Simply put, this is the only references of its kind  among XML books.</Long_x0020_Description> <PriceUS>39.95</PriceUS> </books> <books> <ISBN>0596002378</ISBN> <Title>SAX2</Title> <Tagline>Processing XML Efficiently with Java</Tagline> <Short_x0020_Description>This concise book gives you the information you need to  effectively use the Simple API for XML, the dominant API for efficient XML processing with  Java.</Short_x0020_Description> <Long_x0020_Description>This concise book gives you the information you need to  effectively use the Simple API for XML, the dominant API for efficient XML processing with Java.</Long_x0020_Description> <PriceUS>29.95</PriceUS> </books> <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> <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> <books> <ISBN>0596003722</ISBN> <Title>XSLT Cookbook</Title> <Tagline>Solutions and Examples for XML and XSLT Developers</Tagline> <Short_x0020_Description>A comprehensive collection of recipes for applying XSLT in a  variety of situations.</Short_x0020_Description> <Long_x0020_Description>A comprehensive collection of recipes for applying XSLT in a  variety of situations, including structural changes, and conversion to XHTML, SVG, and  programming code.</Long_x0020_Description> <PriceUS>34.95</PriceUS> </books> </dataroot>

The facilities Access provides for getting XML into and out of databases, while not especially flexible, should be enough for you to transfer data among databases easily.