8.2 Exporting XML from Access Using the GUI

Exporting XML from Access is much like exporting any other format from Access, though with a few extra pieces. There are a number of possible variations in the export process, depending on whether you need to export a single table, a linked group of tables, or a query.

8.2.1 Exporting a Single Table

For our initial example, we'll start with a database containing a table that defines a list of books. The design view for that table is shown in Figure 8-1. It includes six fields of three different types.

Figure 8-1. A simple table for export

For the initial tests, there's just a little bit of information in this table. Exporting mature tables with thousands of records can produce large XML files very quickly definitely useful in real life but difficult for initial analysis. Figure 8-2 shows a partial view of the content in the test table.

Figure 8-2. Test data in the books table

Exporting this table to XML involves a few steps, most of which will be familiar to developers who have exported information from Access databases before. The process starts by selecting the books table in the database, then selecting Export . . . from the File menu. The dialog box shown in Figure 8-3 will appear, and you'll need to select "XML (*.xml)" from the "Save as type" drop-down box.

Figure 8-3. Selecting the destination for the export

When you perform the export, Access may actually create more files than just the XML file, but they'll all appear in the same directory with the XML. Once you click the Export button, a small dialog box with basic options, shown in Figure 8-4, will appear.

Figure 8-4. Basic export options

For now, we'll accept the defaults and just hit OK. The result will be two files, books.xml and books.xsd. The books.xml file will contain the information from the table, while books.xsd will contain an XML Schema description of that content, annotated with a bit of information specific to Access and its Jet database engine.

The books.xml file, shown in Example 8-1, reflects the structure and content of the original table closely.

Example 8-1. A simple table export

<?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="books.xsd" generated="2003-03- 26T13:49:17"> <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> </dataroot>

The root element of this document, dataroot, is the only piece of this document specific to Access:

<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3. org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="books.xsd"  generated="2003-03-26T13:49:17">

It makes a namespace declaration for the od prefix, which is not actually used in this document, and it also includes a pointer to the XML Schema describing this document's structure. Because the element names used here are not in any namespace, the document uses the xsi:noNamespaceSchemaLocation attribute to identify the schema that should be used for all of the elements in this document that have no namespace. It also includes one small bit of metadata in the generated attribute, identifying the time and date when this XML document was created.

The dataroot element contains three child books elements, each indicating a row in the books table. Their contents map fairly simply to the names and values of the table columns:

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

The only significant variation here involves the column names which included spaces. Instead of Short Description, we now have Short_x0020_Description, following a convention Microsoft has developed for representing spaces in XML element names. (XML forbids spaces in element names, as they make it difficult to separate the element name from the attributes, so Access uses _x0020_, the Unicode hex number for the space.)

The XML itself is pretty simple, and provides relatively little information about many of the things Access considers important, like datatype, length, and all the details you can set in the Design view for tables. That information is kept in the XML Schema, shown in Example 8-2.

Example 8-2. The schema Access created to describe its XML output

<?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="books" minOccurs="0" maxOccurs="unbounded"/> </xsd:sequence> <xsd:attribute name="generated" type="xsd:dateTime"/> </xsd:complexType> </xsd:element> <xsd:element name="books"> <xsd:annotation> <xsd:appinfo> <od:index index-name="PrimaryKey" index-key="ISBN " primary="yes" unique="yes"  clustered="no"/> </xsd:appinfo> </xsd:annotation> <xsd:complexType> <xsd:sequence> <xsd:element name="ISBN" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="11"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="Title" 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="Tagline" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="100"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="Short_x0020_Description" minOccurs="0" od:jetType="memo" od: sqlSType="ntext"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="536870910"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="Long_x0020_Description" minOccurs="0" od:jetType="memo" od: sqlSType="ntext"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="536870910"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="PriceUS" minOccurs="0" od:jetType="currency" od:sqlSType="money"  type="xsd:double"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>

The xsd:schema element includes the namespace for XSD itself as well as a namespace declaration for the additional Access-specific information that is used in the schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:od="urn:schemas- microsoft-com:officedata">

The next item is the declaration for the dataroot element. While Access always uses a dataroot element for its exports, the contents of that dataroot element vary from export to export. In this particular case, the dataroot element may contain zero or more books elements, as well as a dateTime attribute called generated:

<xsd:element name="dataroot"> <xsd:complexType> <xsd:sequence> <xsd:element ref="books" minOccurs="0" maxOccurs="unbounded"/> </xsd:sequence> <xsd:attribute name="generated" type="xsd:dateTime"/> </xsd:complexType> </xsd:element>

The remainder of the schema is the declaration for the books element, which itself contains the declarations for all of its child elements. (This style of schema is frequently referred to as "Russian doll," after the nesting wooden dolls, and works well for simple structures like those created here.) The declaration begins with an annotation used by Access to identify the primary key of the table:

<xsd:element name="books"> <xsd:annotation> <xsd:appinfo> <od:index index-name="PrimaryKey" index-key="ISBN " primary="yes" unique="yes"  clustered="no"/> </xsd:appinfo> </xsd:annotation>

Schemas permit any kind of markup in the xsd:appinfo element, and Microsoft has used that freedom along with an index element in its own od namespace to provide information Access can use to reconstruct the primary key.

The next element is an xsd:complexType, which contains an xsd:sequence containing the declarations for all of the child elements that appear in a books element. All of the child elements are declared using xsd:element elements that contain xsd:simpleType elements detailing the restrictions on the content of that particular component. For instance, the declaration for the ISBN element looks like:

<xsd:element name="ISBN" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="11"/> </xsd:restriction> </xsd:simpleType> </xsd:element>

Most of this is basic XML Schema, saying that this is an element named ISBN, which may or may not appear, and whose contents are a string whose maximum length is eleven characters. The xsd:element itself contains two extra attributes, both of them Microsoft-specific. The first, od:jetType, identifies the type of this field in Access, while the second, od:sqlSType, identifies its type for Microsoft SQL Server.

Most of the other elements declared here follow a similar pattern with different xsd:maxLength values; those for the memo-typed values are especially large. One notably different declaration is that for the PriceUS element, which is done using attributes exclusively:

<xsd:element name="PriceUS" minOccurs="0" od:jetType="currency" od:sqlSType="money"  type="xsd:double"/>

In this case, the type of xsd:double is enough to define the contents of the element no further restrictions are needed, so no xsd:simpleType, xsd:restriction, or facet-specific elements are needed. W3C XML Schema has no notion of a currency type, so the data will be stored without a dollar sign. If you need to indicate explicitly that these are U.S. dollars, you may want to add a separate column to the table indicating the units used by the currency.

8.2.2 Exporting Linked Tables

Exporting individual tables is useful, but there are times when you may want to export multiple tables and preserve the relationships between them. Access allows you to export a set of tables, though it works most easily when only two tables are involved.

For our first example, we'll add a table that contains information about (very fictional) promotions for the various books. Figure 8-5 shows what this table looks like.

Figure 8-5. The promotions table

The promotions table links to the books table though its BookID field, as shown in Figure 8-6.

Figure 8-6. Relationships between the books and promotions tables

Exporting this pair of tables takes a few more steps, as Access lets you choose how the export works. The choice of which table is the base table makes a big difference in the results of the export, so the examples below will export it both ways. First, we'll start by exporting the books table again, but this time, we'll select More Options from the dialog box shown in Figure 8-7.

Figure 8-7. Basic export options

Clicking More Options brings up a larger dialog with many more choices, as shown in Figure 8-8.

Figure 8-8. The full version of the Export XML dialog box

In this case, all the information we need is on the first (Data) tab. Checking the "promotions" box and hitting the OK button tells Access to export both the books table and the linked records of the promotions table, in this case, all of them. Example 8-3 shows an abbreviated version of the export, with the new content from the promotions table in bold.

Example 8-3. Exported linked tables

<?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="ch0804.xsd" generated="2003-03- 31T16:37:01"> <books> <ISBN>0596005385</ISBN> <Title>Office 2003 XML Essentials</Title> <Tagline>Integrating Office with the World</Tagline> <Short_x0020_Description>...</Short_x0020_Description> <Long_x0020_Description>...</Long_x0020_Description> <PriceUS>34.95</PriceUS> <promotions> <PromotionID>1</PromotionID> <BookID>0596005385</BookID> <Name>Palm civet bonus</Name> <Venue>Anywhere interested</Venue> <Description>A stuffed-animal palm civet, lovingly screen-printed to match the cover, with  every copy of the book.</Description> <Cost>10000</Cost> </promotions> <promotions> <PromotionID>3</PromotionID> <BookID>0596005385</BookID> <Name>Key chains</Name> <Venue>Conferences</Venue> <Description>keychains adorned with lovely palm civets and the title of the book.</Description> <Cost>1000</Cost> </promotions> </books> <books> <ISBN>0596002920</ISBN> <Title>XML in a Nutshell, 2nd Edition</Title> <Tagline>A Desktop Quick Reference</Tagline> <Short_x0020_Description>...</Short_x0020_Description> <Long_x0020_Description>...</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>...</Short_x0020_Description> <Long_x0020_Description>...</Long_x0020_Description> <PriceUS>29.95</PriceUS> <promotions> <PromotionID>2</PromotionID> <BookID>0596002378</BookID> <Name>Free filters</Name> <Venue>Online/Safari</Venue> <Description>Bonus SAX filters, open source-licensed, for developers who visit the SAX2  book site.</Description> <Cost>0</Cost> </promotions> </books> </dataroot>

The general pattern here is much like the original export of the books table, except that zero or more promotions elements whose BookID holds the same value as the containing books element's ISBN element now appear inside of each books element. This works the same way that zero or more books elements appeared inside of the dataroot element. All of the table columns are listed inside of each promotions element, making it easy to reconstruct the information in the promotions table or to treat the information as a complete set of information about each book.

The schema has also changed only a little, as shown in Example 8-4.

Example 8-4. A schema for a set of related tables

<?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="books" minOccurs="0" maxOccurs="unbounded"/> </xsd:sequence> <xsd:attribute name="generated" type="xsd:dateTime"/> </xsd:complexType> </xsd:element> <xsd:element name="books"> <xsd:annotation> <xsd:appinfo> <od:index index-name="PrimaryKey" index-key="ISBN " primary="yes" unique="yes"  clustered="no"/> </xsd:appinfo> </xsd:annotation> <xsd:complexType> <xsd:sequence> <xsd:element name="ISBN" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="11"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="Title" 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="Tagline" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="100"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="Short_x0020_Description" minOccurs="0" od:jetType="memo"     od:sqlSType="ntext"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="536870910"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="Long_x0020_Description" minOccurs="0" od:jetType="memo"     od:sqlSType="ntext"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="536870910"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="PriceUS" minOccurs="0" od:jetType="currency"     od:sqlSType="money" type="xsd:double"/> <xsd:element ref="promotions" minOccurs="0" maxOccurs="unbounded"/> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element name="promotions"> <xsd:annotation> <xsd:appinfo> <od:index index-name="PrimaryKey" index-key="PromotionID " primary="yes"    unique="yes" clustered="no"/> <od:index index-name="BookID" index-key="BookID " primary="no" unique="no"     clustered="no"/> <od:index index-name="bookspromotions" index-key="BookID " primary="no"     unique="no" clustered="no"/> <od:index index-name="PromotionID" index-key="PromotionID " primary="no"     unique="no" clustered="no"/> </xsd:appinfo> </xsd:annotation> <xsd:complexType> <xsd:sequence> <xsd:element name="PromotionID" minOccurs="1" od:jetType="autonumber"     od:sqlSType="int" od:autoUnique="yes" od:nonNullable="yes" type="xsd:int"/> <xsd:element name="BookID" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="11"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="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="Venue" 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="Description" minOccurs="0" od:jetType="memo" od:sqlSType="ntext"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="536870910"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="Cost" minOccurs="0" od:jetType="currency" od:sqlSType="money"  type="xsd:double"/> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema>

The declaration of the books element is the same as it was, except that it now includes an xsd:element that references the promotions element:

<xsd:element ref="promotions" minOccurs="0" maxOccurs="unbounded"/>

Because there may be more than one promotions element related to each book, the maxOccurs attribute is set to unbounded. The use of a ref attribute to connect to the definition of the promotions element is a change from the prior approach, which made all of these definitions in place. (This is pretty much a style choice the earlier "Russian doll" approach would have worked as well.)

After the closing of the xsd:element element defining the books field, the declaration of the promotions element appears:

<xsd:element name="promotions">

The first feature of the promotions element is an annotation that includes information about the indexes for the promotions table, including a "bookspromotions" index on BookID, which is the connection between the books table and the promotions table.

<xsd:annotation> <xsd:appinfo> <od:index index-name="PrimaryKey" index-key="PromotionID " primary="yes" unique="yes"    clustered="no"/> <od:index index-name="BookID" index-key="BookID " primary="no" unique="no"    clustered="no"/> <od:index index-name="bookspromotions" index-key="BookID " primary="no" unique="no"    clustered="no"/> <od:index index-name="PromotionID" index-key="PromotionID " primary="no" unique="no"    clustered="no"/> </xsd:appinfo> </xsd:annotation>

The contents of the promotions element are defined, just like those of the books element, in an xsd:complexType containing a sequence of declarations:

<xsd:complexType> <xsd:sequence> <xsd:element name="PromotionID" minOccurs="1" od:jetType="autonumber" od: sqlSType="int" od:autoUnique="yes" od:nonNullable="yes" type="xsd:int"/>

Apart from the indexing information, these are pretty ordinary XML schemas, and the structures they describe are typical of XML data. There is very little to their structure that requires interpretation beyond "this books element contains these promotions, so I'll bet those promotions go with that book." Access can't, however, make that work for many-to-one relationships. If, for instance, you used promotions as the primary table for export instead of books, you'd be exporting a many-to-one relationship rather than one-to-many. As Figure 8-9 shows, Access warns you of the difference with an intermediary entry named [Lookup Data], indicating that it will effectively be creating a lookup table to connect the information.

Figure 8-9. Exporting related tables with a many-to-one relationship

Example 8-5 shows the results of exporting the promotions table and the books table, but using the promotions table as the primary table.

Example 8-5. The export of tables related as many-to-one

<?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="promotions.xsd" generated="2003- 04-01T20:32:49"> <promotions> <PromotionID>1</PromotionID> <BookID>0596005385</BookID> <Name>Palm civet bonus</Name> <Venue>Anywhere interested</Venue> <Description>A stuffed-animal palm civet, lovingly screen-printed to match the cover, with  every copy of the book.</Description> <Cost>10000</Cost> </promotions> <promotions> <PromotionID>2</PromotionID> <BookID>0596002378</BookID> <Name>Free filters</Name> <Venue>Online/Safari</Venue> <Description>Bonus SAX filters, open source-licensed, for developers who visit the SAX2  book site.</Description> <Cost>0</Cost> </promotions> <promotions> <PromotionID>3</PromotionID> <BookID>0596005385</BookID> <Name>Key chains</Name> <Venue>Conferences</Venue> <Description>keychains adorned with lovely palm civets and the title of the book.</ Description> <Cost>1000</Cost> </promotions> <books> <ISBN>0596005385</ISBN> <Title>Office 2003 XML Essentials</Title> <Tagline>Integrating Office with the World</Tagline> <Short_x0020_Description>...</Short_x0020_Description> <Long_x0020_Description...</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>...</Short_x0020_Description> <Long_x0020_Description>...</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>...</Short_x0020_Description> <Long_x0020_Description>...</Long_x0020_Description> <PriceUS>29.95</PriceUS> </books> </dataroot>

The connections between the tables are no longer represented in the XML structures themselves; you have to know that BookID and ISBN are connected to make the connections yourself. Once again, that information appears in the exported schema, as shown in the fragment in Example 8-6.

Example 8-6. The declarations for the promotions element and its index annotations

<xsd:element name="promotions"> <xsd:annotation> <xsd:appinfo> <od:index index-name="PrimaryKey" index-key="PromotionID " primary="yes" unique="yes" clustered="no"/> <od:index index-name="BookID" index-key="BookID " primary="no" unique="no"    clustered="no"/> <od:index index-name="bookspromotions" index-key="BookID " primary="no"    unique="no" clustered="no"/> <od:index index-name="PromotionID" index-key="PromotionID " primary="no"    unique="no" clustered="no"/> </xsd:appinfo> </xsd:annotation> <xsd:complexType>...

The last table export pattern we'll explore involves a many-to-many relationship between authors and books. As shown in Figure 8-10, this relationship is implemented with an intermediary table, which permits many authors to work on many books.

Figure 8-10. Related tables with a many-to-many relationship, expressed as two one-to-many relationships

Access will let you traverse this relationship in an XML export, as shown in Figure 8-11.

Figure 8-11. Exporting related tables with a many-to-many relationship

This time the export will use both of the styles shown above, whether you start by exporting the authors table with the books table, because the style of the export is determined by the nature of the join. One-to-many relationships are represented using containment, while many-to-one relationships are represented as separate pieces. In this case, the many-to-many relationship includes both of those choices.

Once again, the [Lookup Data] provides a warning that reassembling some of these relationships is going to require extra lookup work on the part of the consuming application. (Access does this extra work automatically, as we'll see in Section 8.3, later in this chapter.) The results of this export are structurally a combination of our earlier exports, as shown in Example 8-7.

Example 8-7. A many-to-many export combining containment and lookup

<?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="ch0806.xsd" generated="2003-04- 01T21:01:50"> <books> <ISBN>0596005385</ISBN> <Title>Office 2003 XML Essentials</Title> <Tagline>Integrating Office with the World</Tagline> <Short_x0020_Description>...</Short_x0020_Description> <Long_x0020_Description>...</Long_x0020_Description> <PriceUS>34.95</PriceUS> <authorBookLink> <bookISBN>0596005385</bookISBN> <authorID>1</authorID> </authorBookLink> </books> <books> <ISBN>0596002920</ISBN> <Title>XML in a Nutshell, 2nd Edition</Title> <Tagline>A Desktop Quick Reference</Tagline> <Short_x0020_Description>...</Short_x0020_Description> <Long_x0020_Description>...</Long_x0020_Description> <PriceUS>39.95</PriceUS> <authorBookLink> <bookISBN>0596002920</bookISBN> <authorID>3</authorID> </authorBookLink> <authorBookLink> <bookISBN>0596002920</bookISBN> <authorID>4</authorID> </authorBookLink> </books> <books> <ISBN>0596002378</ISBN> <Title>SAX2</Title> <Tagline>Processing XML Efficiently with Java</Tagline> <Short_x0020_Description>...</Short_x0020_Description> <Long_x0020_Description>...</Long_x0020_Description> <PriceUS>29.95</PriceUS> <authorBookLink> <bookISBN>0596002378</bookISBN> <authorID>2</authorID> </authorBookLink> </books> <authors> <AuthorID>1</AuthorID> <GivenName>Simon</GivenName> <FamilyName>St.Laurent</FamilyName> <FullName>Simon St.Laurent</FullName> </authors> <authors> <AuthorID>2</AuthorID> <GivenName>David</GivenName> <FamilyName>Brownell</FamilyName> <FullName>David Brownell</FullName> </authors> <authors> <AuthorID>3</AuthorID> <GivenName>Elliotte</GivenName> <FamilyName>Harold</FamilyName> <FullName>Elliotte Rusty Harold</FullName> </authors> <authors> <AuthorID>4</AuthorID> <GivenName>Scott</GivenName> <FamilyName>Means</FamilyName> <FullName>W. Scott Means</FullName> </authors> </dataroot>

Each of the books elements now contains one or more authorBookLink elements that hold an authorID element. The value of that authorID element maps to an AuthorID element inside of an authors element. It takes a little traversing and sorting to reach an author's name from a book, but the connections are all still intact.

8.2.3 Exporting a Query

All this traversing isn't much fun for developers used to working with XML's container approach. Fortunately, relational databases have long offered another choice for interacting with their information: queries that provide specific views of information. Queries don't by themselves provide nested views, but they certainly make it easier to present some kinds of information, notably that with many-to-many relationships. The mechanics of exporting queries are much like those of exporting single tables, and the results are similar.

Access supports SQL queries, obviously, as that's at the heart of its functionality. Access does not, however, support other standards for querying, like XQuery.

To demonstrate, we'll export a SQL query named booksByAuthor, which uses the books, authorBookLink, and authors tables to create a list of books sorted by author. The SQL for the query expresses the relationships that an XML processor working with the linked table export would otherwise have to deal with:

SELECT authors.GivenName, authors.FamilyName, books.ISBN, books.Title FROM books INNER JOIN (authors INNER JOIN authorBookLink ON authors.AuthorID =    authorBookLink.authorID) ON books.ISBN = authorBookLink.bookISBN ORDER BY authors.FamilyName;

The interface for exporting a query is exactly the same as that for a table, except that there is no option for exporting linked information. When you export a query, all the information you want to export must be in that query. Exporting the query produces the result shown in Example 8-8.

Example 8-8. An exported query

<?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="booksByAuthor.xsd"  generated="2003-04-02T14:47:59"> <booksByAuthor> <GivenName>David</GivenName> <FamilyName>Brownell</FamilyName> <ISBN>0596002378</ISBN> <Title>SAX2</Title> </booksByAuthor> <booksByAuthor> <GivenName>Elliotte</GivenName> <FamilyName>Harold</FamilyName> <ISBN>0596002920</ISBN> <Title>XML in a Nutshell, 2nd Edition</Title> </booksByAuthor> <booksByAuthor> <GivenName>Scott</GivenName> <FamilyName>Means</FamilyName> <ISBN>0596002920</ISBN> <Title>XML in a Nutshell, 2nd Edition</Title> </booksByAuthor> <booksByAuthor> <GivenName>Simon</GivenName> <FamilyName>St.Laurent</FamilyName> <ISBN>0596005385</ISBN> <Title>Office 2003 XML Essentials</Title> </booksByAuthor> </dataroot>

Just as in a tabular representation of the query, information repeats, notably the ISBN and title of XML in a Nutshell, which has two authors. The schema exported for queries follows the same pattern as exports of a single table.

8.2.4 Presentation and Transformation

While the XML export features described above are certainly useful, the export formats shown are really only the beginning of what you can do. These formats represent the limits of what Access itself understands, but Access also provides hooks for other approaches, including a presentation form for web browsers and much broader capabilities for XSLT integration.

Access' support for XSLT transformations on export works only when you export data using the GUI interface.

We'll transform the result of the query export shown above in Example 8-8 using an XSLT stylesheet. The stylesheet itself, shown in Example 8-9, is extremely simple, merely creating paragraphs and adding labels. The most exciting thing that happens is that the authors' GivenName and FamilyName end up on the same line, separated by a space.

Example 8-9. A simple stylesheet for producing HTML from the booksByAuthor query

<xsl:stylesheet version="1.0"    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" > <xsl:output method="xml" omit-xml-declaration="yes"     encoding="US-ASCII"/> <xsl:template match="dataroot" > <html>       <head>     <title>Exported Query</title>   </head>       <body>     <xsl:for-each select="booksByAuthor">          <p>        <xsl:text>Author: </xsl:text>        <xsl:value-of select="GivenName"/>        <xsl:text> </xsl:text>        <xsl:value-of select="FamilyName"/>      </p>          <p>ISBN: <xsl:value-of select="ISBN"/></p>          <p>Title: <xsl:value-of select="Title"/></p>      <hr />     </xsl:for-each>       </body> </html>     </xsl:template>     </xsl:stylesheet>

To apply this transformation to the data, follow the same process for exporting it normally, until you reach the Export XML dialog box shown previously in Figure 8-11. Here, you click the Transforms . . . button, revealing the dialog box shown in Figure 8-12.

Figure 8-12. The Transforms dialog box

Click the Add . . . button, and you can browse your filesystem to add an XSLT stylesheet to your options. Once you've done that, you can select a transformation and click OK.

This time, when you perform the export, Access applies the XSLT stylesheet to the outgoing data, producing the result shown in Example 8-10.

Example 8-10. Results of an XSLT-enhanced export

<html> <head><title>Exported Query</title></head> <body> <p>Author: David Brownell</p> <p>ISBN: 0596002378</p> <p>Title: SAX2</p> <hr/> <p>Author: Elliotte Harold</p> <p>ISBN: 0596002920</p> <p>Title: XML in a Nutshell, 2nd Edition</p> <hr/> <p>Author: Scott Means</p> <p>ISBN: 0596002920</p> <p>Title: XML in a Nutshell, 2nd Edition</p> <hr/><p>Author: Simon St.Laurent</p> <p>ISBN: 0596005385</p> <p>Title: Office 2003 XML Essentials</p> <hr/> </body> </html>

In a web browser, this looks like Figure 8-13.

Figure 8-13. Transformed query results in a web browser

Unfortunately, Access will produce a blank file if the stylesheet includes <xsl:output method="html" />, so the HTML produced by this method will only work in more recent browsers.

These foundations will let you bypass the Access reports and HTML generation capabilities if you want to create custom reports, web views, or share information with systems that don't find the XML that Access generates directly amenable.