Section 12.3.  Exporting Access tables

Prev don't be afraid of buying books Next

12.3. Exporting Access tables

Exporting Access table data as XML generates three kinds of files, all of them optional. They are:

  1. A data file, with a .xml extension, that contains the data from the contents of the table(s).

  2. A schema document, with a .xsd extension, which describes the structure of the data file.

  3. A pair of presentation files, with the extensions .xsl and either .htm or .asp, which will display the data in a browser.

12.3.1 A simple export

First, let's look at a simple export of a single table using all the default settings. To do this:

1. Select the table you want to export from the list of tables. In this case, select ORDERS.

2. On the File menu, click Export.

3. Select XML (*.xml) from the Save as type list.

4. The File name box will default to the table name. Use the default value or type a different name into the box. Do not use a file extension; if you type a file extension other than xml, Access will append another file extension to it.

5. This will bring up the Export XML dialog shown in Figure 12-5. The three options shown in this dialog represent the three optional components to be generated. For now, leave the default settings (with the first two check boxes checked).

Figure 12-5. The Export XML dialog




This simple export saves two files: in our example, ORDERS.xml and ORDERS.xsd.

12.3.1.1 The exported XML document

The generated XML data document, ORDERS.xml, is shown in Example 12-1. [1]

[1] Extra line breaks and spaces were added to make it easier to read.

Example 12-1. The exported XML (ORDERS.xml)
 <?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="ORDERS.xsd"           generated="2004-02-26T16:53:27">   <ORDERS>     <ORD_NUM>1001</ORD_NUM>     <DATE>2004-02-15T00:00:00</DATE>     <CUST_NAME>Doug Jones</CUST_NAME>     <ADDRESS>123 Main St.</ADDRESS>     <CITY>Arlington</CITY>     <STATE>VA</STATE>     <ZIP>22205</ZIP>   </ORDERS>   <ORDERS>     <ORD_NUM>1002</ORD_NUM>     <DATE>2004-03-23T00:00:00</DATE>     <CUST_NAME>Monica Lyle</CUST_NAME>     <ADDRESS>443 Elm Road</ADDRESS>     <CITY>Traverse City</CITY>     <STATE>MI</STATE>     <ZIP>49684</ZIP>   </ORDERS>   <ORDERS>     <ORD_NUM>1003</ORD_NUM>     <DATE>2004-04-12T00:00:00</DATE>     <CUST_NAME>Marla Worthington</CUST_NAME>     <ADDRESS>12 Jeremy Street</ADDRESS>     <CITY>Moraga</CITY>     <STATE>CA</STATE>     <ZIP>94556</ZIP>   </ORDERS> </dataroot> 

The structure of the XML document closely parallels the structure of the table. There is a root element, dataroot, which contains one or more ORDERS elements. Each ORDERS element represents a row in the table. It contains a child element for each column in the table, for example ORD_NUM and DATE. The names used in the XML document (with the exception of dataroot) are taken directly from the Access table design.

Each of these children contains the data contained in that column for that row. If there is no value for that column in that row, an empty element is generated. The format of the data is dependent on the type of the column. In our example, the content of each DATE element is formatted as YYYY-MM-DDTHH:MM:SS, which is the XML Schema dateTime format that corresponds to the Date/Time type in Access.

12.3.1.2 The generated schema

The generated schema, ORDERS.xsd, is shown in Example 12-2.

Example 12-2. The generated schema (ORDERS.xsd)
 <?xml version="1.0" encoding="UTF-8"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"             xmlns:od="urn:schemas-microsoft-com:officedata">   <xsd:element name="dataroot"><xsd:complexType><xsd:sequence>     <xsd:element ref="ORDERS" minOccurs="0" maxOccurs="unbounded"/>   </xsd:sequence></xsd:complexType></xsd:element>   <xsd:element name="ORDERS">     <xsd:annotation><xsd:appinfo>       <od:index index-name="PrimaryKey" index-key="ORD_NUM"            primary="yes" unique="yes" clustered="no"/>       <od:index index-name="CUST_NUM" index-key="CUST_NAME"            primary="no" unique="no" clustered="no"/>       <od:index index-name="ORDER_NUM" index-key="ORD_NUM"            primary="no" unique="no" clustered="no"/>     </xsd:appinfo></xsd:annotation>     <xsd:complexType><xsd:sequence>       <xsd:element name="ORD_NUM" minOccurs="0" od:jetType=                    "longinteger" od:sqlSType="int" type="xsd:int"/>       <xsd:element name="DATE" minOccurs="0" od:jetType="datetime"                    od:sqlSType="datetime" type="xsd:dateTime"/>       <xsd:element name="CUST_NAME" minOccurs="0" od:jetType="text"                                     od:sqlSType="nvarchar">            <xsd:simpleType><xsd:restriction base="xsd:string">                            <xsd:maxLength value="50"/>            </xsd:restriction></xsd:simpleType></xsd:element>       <xsd:element name="ADDRESS" minOccurs="0" od:jetType="text"                                   od:sqlSType="nvarchar">            <xsd:simpleType><xsd:restriction base="xsd:string">                            <xsd:maxLength value="50"/>            </xsd:restriction></xsd:simpleType></xsd:element>       <xsd:element name="CITY" minOccurs="0" od:jetType="text"                                od:sqlSType="nvarchar">            <xsd:simpleType><xsd:restriction base="xsd:string">                            <xsd:maxLength value="50"/>            </xsd:restriction></xsd:simpleType></xsd:element>       <xsd:element name="STATE" minOccurs="0" od:jetType="text"                                 od:sqlSType="nvarchar">            <xsd:simpleType><xsd:restriction base="xsd:string">                            <xsd:maxLength value="50"/>            </xsd:restriction></xsd:simpleType></xsd:element>       <xsd:element name="ZIP" minOccurs="0" od:jetType="text"                               od:sqlSType="nvarchar">            <xsd:simpleType><xsd:restriction base="xsd:string">                            <xsd:maxLength value="50"/>            </xsd:restriction></xsd:simpleType></xsd:element>     </xsd:sequence></xsd:complexType>   </xsd:element> </xsd:schema> 

The schema describes the structure of the generated XML document. It first contains the dataroot element declaration, which specifies that a dataroot element can contain zero, one or many ORDERS children. The ORDERS element declaration appears next, along with embedded declarations for the four allowed children.

The generated schema defines simple types for some of the elements that contain the data. For columns whose type is Text in Access, such as the CITY column, it generates a type that is a string whose length can be up to 50 characters. In other cases, for example the DATE column, it simply uses XML Schema built-in types like dateTime. Additional Access-specific type information is included in the schema using attributes that are prefixed with od. These additional attributes are used by Access if you re-import the data into an Access database.

The generated schema also contains information about the primary keys and indexes on the table. This information is included in the xsd:appinfo element, within the declaration of ORDERS.

12.3.1.3 Additional options

You can have more control over how the XML-related components are generated by clicking More Options on the Export XML dialog. This brings up a new Export XML dialog with three tabs, one each for Data, Schema and Presentation. These options are discussed in following sections.

12.3.2 Exporting data: a closer look

In our previous example, we exported all the data from a single table. There are additional options which allow you to export data from more than one related table at once, and allow you to filter the data that is exported. These options are specified on the Data tab of the expanded Export XML dialog, shown in Figure 12-6.

Figure 12-6. Export XML Data options




This dialog has several sections:

  • The Data to Export section allows you to select the tables you want to export. This is described in 12.3.2.1, "Selecting tables to export", on page 276.

  • The Records To Export section at the top right allows you to choose the specific records you want to export. This is described in 12.3.2.2, "Exporting using filters and sorts", on page 278.

  • The Apply Existing Sort check box allows you to specify a sort. This is described in 12.3.2.2, "Exporting using filters and sorts", on page 278.

  • The Transforms button allows you to select a transformation to apply to the data upon export. This is described in 12.5, "Applying a transform on export", on page 285.

  • The Encoding list allows you to specify an encoding for your XML document.

  • The Export Location section allows you to choose the location where your XML document will be saved.

12.3.2.1 Selecting tables to export

The Data to Export section of the Data tab shows the tables in the database in a tree structure. At the top is the table that was selected when you initiated the export. If you wish to start with a different table, you can exit the dialog, select a different table, and restart the export.

Access constructs the hierarchy of tables based on primary and foreign key relationships in the database. Only tables related to the table of interest are displayed in the hierarchy. A [Lookup Data] node appears when following a many-to-one relationship, for example from ORDER_ITEMS to PRODUCTS.

Each table is accompanied by a check box that allows you to select the tables that you want to export. You can select any or all of the tables to export, and it will export the data from all the selected tables to the same XML document. Access will join the tables based on the joins in the database. If you choose to export all three tables, a fragment of the resulting XML document is shown in Example 12-3.

Example 12-3. Generated XML data from related tables (ORDERS all tables.xml)
 <ORDERS>   <ORD_NUM>1001</ORD_NUM>   <DATE>2004-02-15T00:00:00</DATE>   <CUST_NAME>Doug Jones</CUST_NAME>   <ADDRESS>123 Main St.</ADDRESS>   <CITY>Arlington</CITY>   <STATE>VA</STATE>   <ZIP>22205</ZIP>   <ORDER_ITEMS>     <ORDER_NUM>1001</ORDER_NUM>     <PROD_NUM>219</PROD_NUM>     <QUANTITY>1</QUANTITY>     <SIZE>L</SIZE>   </ORDER_ITEMS>   <ORDER_ITEMS>     <ORDER_NUM>1001</ORDER_NUM>     <PROD_NUM>334</PROD_NUM>     <QUANTITY>1</QUANTITY>     <SIZE>L</SIZE>   </ORDER_ITEMS> </ORDERS> <!--...--> <PRODUCTS>   <NUM>233</NUM>   <NAME>Silk 2-Pocket Blouse</NAME>   <PRICE>59.99</PRICE>   <DEPT>WOMEN</DEPT> </PRODUCTS> <PRODUCTS>   <NUM>241</NUM>   <NAME>Deluxe Golf Umbrella</NAME>   <PRICE>39.99</PRICE>   <DEPT>ACCESSORY</DEPT> </PRODUCTS> <PRODUCTS> <!--...--> 

The XML document contains ORDERS elements just as before. However, they now contain one or more ORDER_ITEMS elements that are related to that ORDERS row by the key relationship. In addition, PRODUCTS elements contain the product information. The PRODUCTS are listed separately at the end of the document rather than contained in the ORDER_ITEMS elements. This is because of the cardinality of the relationship in the database design.

The newly generated schema reflects the new structure of the document. It contains declarations for all of the element types in ORDERS, ORDER_ITEMS and PRODUCTS.

12.3.2.2 Exporting using filters and sorts

The Records To Export section of the Data tab allows you to choose the specific records you want to export. There are three choices:

  • All Records exports all the records in the table(s), as we have done in our previous examples.

  • Apply existing filter allows you to apply a filter to the export. If a filter exists for the table you have chosen, and you choose this option, it will be applied on export. If no filter exists for the table, this option cannot be selected.

  • Current record allows you to export the current record. You can only choose this option if you had a table open with a record selected when you initiated the export process. Access will export the current record, along with any related records in other tables (for example the ORDER_ITEMS elements that relate to that order). If you include a lookup table, as in PRODUCTS, it will still continue to export all the PRODUCTS records, not just the ones related to the exported ORDER_ITEMS.

The Apply Existing Sort check box allows you to specify whether to use the sort that currently exists on the table. A sort exists if, for example, you opened the datasheet view of the ORDERS table, sorted the records by CITY, and saved the table design. Checking the Apply Existing Sort box will cause the exported XML to also be sorted by CITY. If the box is unchecked, the records will be exported in the order in which they are physically stored in the table.

12.3.3 Generating a schema

The schema is useful as a guide to the structure of the exported document. In addition, if you plan to re-import the XML data into another Access database, the schema is essential to ensure that all of the table relationships and data types are preserved upon re-import.

The schema export options are shown in Figure 12-7.

Figure 12-7. The Schema tab of the Export XML dialog




The Export Schema check box indicates whether to export the schema at all; if you do not check this box, no schema will be generated.

The Include primary key and index information check box indicates whether to include the primary key information as an annotation in the schema, as was shown in Example 12-2. This only matters if you want to re-import the data into another Access database, and you want Access to recreate those tables with their key relationships in the other database.

The Export Location section allows you to specify whether Access should embed the schema directly in the XML document that contains the data, or create a separate schema document (the default). In general, you should avoid embedding the schema, since this is not a customary practice and a schema processor may not be able to validate such a document.

12.3.4 Generating a presentation

When you choose to generate a presentation from a table, Access will generate documents that allow you to view the data in a tabular format in a Web browser. This involves two additional files:

  1. an XSLT stylesheet, with the extension .xsl, that transforms the generated XML data to HTML for presentation; and

  2. a file that is used by a Web server to apply the XSLT stylesheet. This file is either an HTML document containing a script, or an Active Server Page (ASP).

An example presentation is shown in Figure 12-8. Note that only the "main" ORDERS table is presented; the data from related tables is omitted. To include the data from the related tables, you would need to write a custom XSLT stylesheet, or use a tool like FrontPage to generate the XSLT based on your requirements.

Figure 12-8. The Presentation file (ORDERS.htm) as shown in a Web browser




There are several options relating to generating the presentations. They can be set in the Presentation options dialog, shown in Figure 12-9.

Figure 12-9. The Presentation tab of the Export XML dialog




The Export Presentation check box indicates whether to export the presentation files at all; if you do not check this, no presentation will be generated.

The Run from section allows you to specify whether the generated transformation is to be run from the client or server. If you choose Client (HTML), it will generate an HTML page, with the file extension .htm. If you choose Server (ASP) it will generate an Active Server Page (ASP), with the file extension .asp.

The Include report images section indicates whether or not to include images. This is only relevant to exporting forms and reports, as described in 12.4.2, "Exporting forms", on page 284.

The Export Location section allows you to specify the location for the XSLT stylesheet file. The related HTML or ASP document is always saved in the same directory as the data, regardless of the location of the stylesheet.

Tip

The presentation generation capabilities of Access are useful for taking a quick look at the exported data. For more powerful presentation capabilities, FrontPage offers a GUI editor for generating Web pages from XML documents. These capabilities are described in Chapter 13, "Publishing XML to the Web with FrontPage", on page 294.




Amazon


XML in Office 2003. Information Sharing with Desktop XML
XML in Office 2003: Information Sharing with Desktop XML
ISBN: 013142193X
EAN: 2147483647
Year: 2003
Pages: 176

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