New XML Features in Microsoft Office Access 2003

  • Article

 

Frank C. Rice
Microsoft Corporation

October 2003

Applies to:
    Microsoft® Office Access 2003

Summary: Explore the XML-related features of Microsoft Office Access 2003. Learn how to transform while importing or exporting XML data. Also learn about exporting related data when exporting to XML data. (9 printed pages)

Download odc_accessnewxmlfeaturessample.exe.

Contents

Introduction
Exporting XML Data
Importing XML Data
Including Related Tables When Exporting XML
Exporting and Transforming XML Programmatically
Conclusion

Introduction

Microsoft® Office Access 2003 features enhanced Extensible Markup Language (XML)-related features. For example, you can now transform XML data into another format with Extensible Stylesheet Language (XSL) files during both import and export operations. In addition, you can now include related tables when exporting XML from a database. These and other features are examined in more detail in this article.

Exporting XML Data

In the last version of Access, you could only export XML data using the schema that Access would generate. However, many times there is a need to transform the data into another format. Access 2003 adds a feature that allows you to specify an XSL-transform to use when exporting XML. The following procedure walks through this process.

To export XML data

  1. Start Microsoft Access 2003 and open the Northwind sample database. To do so, from Access, on the Help menu, point to Sample Databases and then click Northwind Sample Database.
  2. Double-click the Employees table and then on the File menu, click Export.
  3. In the File name box, type Sample.
  4. In the Save as type box, click the arrow and then click XML (*.xml).
  5. Click Export All.
  6. In the Export XML dialog box, click More Options.
  7. On the Data tab, click Transforms.
  8. In the Export Transforms dialog box, click Add.
  9. In the Add New Transform dialog box, browse to the location of the sample files, click the Employees_Out.xsl transform file, and then click Add.
  10. In the Export Transforms dialog box, highlight Employees_Out.xsl and then click OK.
  11. In the Export XML dialog box, click OK. The Employees file is created with the data transformed by using the Employees_Out.xsl transform.

Note   The Employees_Out.xsl file is used to display only the first name and last name from the data in the Employees table.

Figure 1. The Employees_Out.xsl transform file

The following figure shows the Employees output file:

Figure 2. Output from exporting XML by using a transform

Importing XML Data

The previous version of Access allowed you to import XML data into new or existing tables. This version of Access allows you to transform the data before creating new tables or appending to existing tables. The following steps demonstrate transforming a file of employee data during import. This is particularly valuable when trying to import XML and append to existing tables when the XML was not generated from a database with the exact same schema.

To import XML data

  1. Start Microsoft Access 2003 and open the Northwind sample database. To do so, from Access, on the Help menu, point to Sample Databases and then click Northwind Sample Database.

  2. On the File menu, point to Get External Data, and then click Import.

  3. In the Import dialog box, in the Files of type list, click XML (*.xml,*xsd).

  4. If necessary, browse to the location of the sample files and then click the Employees_In.xml****file.

  5. Click Import.

    Note   The Employees_In table was created by exporting the Employees table from the Northwind sample database. The Employee table consists of fields such as first name, last name, title, date of hire, and so forth.

  6. In the Import XML dialog box, under the Tables node, click Employees, click Options, and then click Transform.

  7. In the Import Transforms dialog box, click Add.

  8. In the Add New Transform dialog box, click Employees_In.xsl, and then click Add.

  9. In the Import Transforms dialog box, click Employees_In, click OK, and then click OK again.

  10. Click OK to close the message box.

  11. Double-click the Employee table to open it. The table contains two columns of first and last names. Thus the original Employees table was pared down to two columns of data by using the XSL style sheet transformation file.

Figure 3. The Employees_In.xsl transform file

The following figure shows the new Employee table:

Figure 4. Result from importing XML with a transform

XML is a great format for exchanging data from Access to other applications. Usually this data is located in several different tables. For example, when a list of orders is exported, the list is incomplete unless the corresponding Order Details and Products tables are also exported. Starting with Access 2003, you can export multiple tables and queries of related data together.

Note   Related tables and queries are determined using the relationships defined in the Relationship Window.

To export related tables

  1. Start Microsoft Access 2003 and open the Northwind sample database. To do so, from Access, on the Help menu, point to Sample Databases and then click Northwind Sample Database.

  2. Click the Orders table and then click Export on the File menu.

  3. In the File name box, type Sample.

  4. In the Save as type box, click the arrow and then click XML (*.xml)

  5. Click Export All.

  6. In the Export XML dialog box, click More Options.

  7. Look at dialog box shown in the following figure:

    Figure 5. Export XML dialog box with the Orders table selected

    Notice the Orders and related Order Details table are checked and ready for export. The Order Details table is related to the Orders table by a one-to-many (1-M) relationship, that is, an order can have many items in the Order Details table. Any one-to-one (1-1) relationships would also be displayed here.

    Now notice the node marked Lookup Data. Tables that appear underneath this tab are based on a many-to-one (M-1) relationship. For example, one customer can have many orders.

  8. To finish exporting, click OK.

Exporting and Transforming XML Programmatically

A number of changes were made to the Access object model to add or update functionality when importing or exporting XML programmatically.

ExportXML Method

To keep from breaking solutions that were built on the current version of the ExportXML method, new behavior was added to the method using optional parameters.

ExportXML (ObjectType As AcExportXMLObjectType, Datasource As String,
 [DataTarget As String], [SchemaTarget As String], [PresentationTarget as String],
 [ImageTarget As String], [Encoding As AcExportXMLEncoding], [OtherFlags As Long],
 [WhereCondition As String], [AdditionalData as AdditionalData])

The majority of the parameters existed in the previous version of ExportXML. The updated parameters are as follows:

OtherFlags Optional AcExportXMLOtherFlags. Numeric expression that is the sum of values specifying the other flags that should be used.

AcExportXMLOtherFlags Value
acEmbedSchema 1
acExcludePrimaryKeyAndIndexes 2
acRunFromServer 4
acLiveReportSource 8
acPersistReportML 16

WhereCondition Optional String. If specified and not and empty string, use this like you would the Filter property of an object to construct the WHERE clause.

AdditionalData Optional AdditionalData. This is ignored if acLiveReportSource is specified. Object that describes the other tables to export.

AdditionalData Object

AdditionalData is a collection of AdditionalData objects.

Add Method

AdditionalData.Add(Name As String) As AdditionalData

Adds an AdditionalData object named Name and returns it.

Item Method

AdditionalData.Item(Index) As AdditionalData

Index can be either a number or a string referring to the Name of AdditionalData that was added. Calling the Item method returns the corresponding AdditionalData. If there is no corresponding AdditionalData, the Nothing object is returned.

Count Property

AdditionalData.Count as Long  Read Only

Returns the number of AdditionalData objects that were added.

Name Property

AdditionalData.Name as String   Read Only

Returns the Name of the AdditionalData object as specified in the Add method.

Default

AdditionalData(Index) As AdditionalData

This is the same as AdditionalData.Item(Index) As AdditionalData.

TransformXML Method

Instead of offering DataTransform parameters on the ImportXML and ExportXML methods, the TransformXML method exists as a separate method.

TransformXML (DataSource as String, DataTransform as String, DataTarget as String)

This method simply loads DataSource into a DOMDocument, applies TransformSource, and saves the result to OutputTarget.

DataSource Required String. The name and path of the XML file to import. This can be an HTTP://... path.

TransformSource Required String. The XSL file to apply to the DataSource.

OutputTarget Required String. The file name and path for the results after applying the DataTransform. If DataTarget equals DataSource, the DataSource is overwritten with the output of the DataTransform.

WellFormedXMLOutput Optional Boolean. Default is False. If True, TransformNodeToObject is used. This requires that the output is well-formed XML. If False, TransformNode is used. This does not require that the output be well formed XML.

ScriptOptions Optional AcTransformXMLScriptOptions. Default is acPromptScript. If acPromptScript, a prompt appears before running any script in the transform. If acEnableScript, any script automatically runs. If acDisableScript, any script in the transform is ignored.

Conclusion

In this article, we reviewed the XML features of the latest version of Access. In Microsoft Office Access 2003, you can apply XSL transforms to data on both import and export. You can also export related tables when exporting as XML. And finally, export and transform capabilities are available directly from the object model.