Exporting Content as XML

You can export an entire table from Access in XML format. You can also refine the contents of your database by exporting a query or report. Access uses the field names within the table or query to generate the tag names in the XML document.

Exporting a table object

Whichever type of database object you choose to export, the process is the same. You right-click the object and choose Export . Figure 7-2 shows Access exporting a table.

Figure 7-2: Exporting a table from Access

Access will prompt you for the type of data to export. Choose XML and enter a name and location for the XML document. When you click the Export button, Access asks which files youd like to create; you can choose any of the following: Data , Schema of the data , or Presentation of your data . Figure 7-3 shows the Export XML dialog box.

Figure 7-3: Choosing which files to export from Access

If youre exporting XML content for use in Flash, youll only need to select the first option, Data (XML). Flash is unable to use either an XML schema or XSL style sheet. When you click OK , Access generates the files that youve selected. Figure 7-4 shows an exported XML document in XMLSpy.

Figure 7-4: Exported XML content from Access displayed in XMLSpy

Access creates a root element called <dataroot> and adds a namespace and generated attribute containing a date and timestamp. This is the only content that Access creates in the XML document. All other content comes from existing data within Access. Each data row uses the table name for the tag, which, in the example in Figure 7-4, is <tblDocuments> . The child nodes use the field names from the table, and you can see the elements <documentID> , <documentName> , <authorID> , <documentPublishYear> , and <categoryID> .

Dont include spaces in the field names within Access or youll get some unexpected results when you generate your XML document. For example, if I used the field name book Name , the XML document would include the element <book_x0020_Name> . Access replaces the space with _x0020_ (which is the hexadecimal Unicode representation of a space).

Compare the XML document structure in Figure 7-4 with the table structure that produced the content. Figure 7-5 shows the datasheet view of the table.

Figure 7-5: The datasheet view of tblDocuments

The XML document is a direct reproduction of the table. However, because the data in Access is relational, some of the content may not make sense in the XML document, for example, the <authorID> and <categoryID> elements. An alternative option is to create the table joins within a query and export the query object to an XML document.

Exporting a query

Queries allow you to join related tables, give the fields new names, and create calculated fields. For these reasons, its often better to export a query rather than a table. The process is the same one that you saw earlierright-click the query object and choose Export . You choose the XML type, select which files to create, and click the Export button.

Figure 7-6 shows an Access query in Datasheet view.

Figure 7-6: The Datasheet view of qryBookDetails

This query joins all three tables in the database and produces a simple list. The query also joins the author first name and last name into a single field and replaces the existing field names with more readable alternatives. Figure 7-7 shows the XML document produced by this query. You can see this content in the resource file bookDetails.xml .

Figure 7-7: The qryBookDetails object exported in XML format

You might find this content more useful within Flash, compared to the table export. I could have renamed qryBookDetails to create a friendlier name for each row in the XML document. You can work through this process in the following exercise.

Exercise 1: Generating XML content from Access

1. Open documents.mdb in Access 2003.

2. Select the Queries objects and export the query qryAuthorDocuments in XML format. Select only an XML document and name it authorBooks.xml .

3. Open the new authorBooks.xml file in an XML or text editor and look through the contents. You can also see my completed resource file authorBooks.xml .

4. Experiment with changing the field names within the Access query. You can do this in the design view of the query by adding the new name to the left of the current name with a colon , for example, bookName: documentName . Export the file again to see the updated contents.

In this exercise, youve seen how to export XML content from an Access query. Youve also seen how easy it is to change the names used within the XML document by changing the query field names.

 

Exporting a report

Access allows you to export reports just as you would tables and queries. The exported data will include all fields from the report, regardless of whether you can see them when the report prints out.

Creating a schema from Access

You can generate a schema from Access by selecting the Schema of the data option during the export process. Figure 7-8 shows the Export XML dialog box with this option selected.

Figure 7-8: The qryBookDetails object exported in XML format

Right-click the object, choose Export , select the Schema of the data option, and click the OK button. Figure 7-9 shows the schema that Access generates from a query. You can see the completed bookDetails.xsd in your resource files. Notice that the schema uses the Russian Doll layout, in which elements nest within each other.

Figure 7-9: The schema document generated by Access

As Figure 7-9 shows, Access preserves the field-length restriction in an <xsd:maxLength> element. You can see an example in the <bookName> element here:

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

Access also extends the schema by adding its own namespace:

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

This allows the file to include declarations specific to Access. Within the file, declarations prefaced with the prefix od indicate those specific to Access:

 <xsd:element name="bookName" minOccurs="0"  od:jetType="text"   od:sqlSType="nvarchar">  

The od:jetType attribute indicates the datatype within Access while the od:sqlSType represents the datatype within SQLServer.

Creating a style sheet from Access

Access can also generate an XSL style sheet from your data. In Access, this option transforms XML into HTML. Figure 7-10 shows the Export XML dialog box with this option checked. Youll need to check the Data option as well as the Presentation of your data option.

Figure 7-10: Generating a style sheet from Access

Figure 7-11 shows the style sheet generated after you click the OK button.

Figure 7-11: The style sheet generated from Access shown in XMLSpy

Youll notice that the style sheet transforms the XML data into HTML format. If you look through the transformed document, youll see that the file includes CDATA that contains VBScript. The VBScript block lists a set of functions available to the transformation; you can see the content at the end of the file.

Access also generates the HTML document during the export. Figure 7-12 shows the transformed HTML file. Ive included bookDetails.xsl and bookDetails.htm in your resource files.

Figure 7-12: The transformed HTML document generated by Access

Youll notice that Access presents the data in a simple table format. The heading row shows the element names. You could create your own style sheet if you needed to create a custom transformation. Well generate documents from an Access query in exercise 2.

Exercise 2: Generating XML content from Access

In this exercise, well generate an XML document, schema, and style sheet from an Access query.

1. Open documents.mdb in Access 2003 if necessary.

2. Export the query qryAuthorDocuments as an XML file, schema, and style sheet. Use the name authorDocuments for all the files.

3. Open the documents within an XML or text editor so you can see the contents.

4. Open authorDocuments.htm in a web browser so you can see the contents. Youll find my completed files authorDocuments.htm , authorDocuments.xml , authorDocuments.xsd , and authorDocuments.xsl with your resources.

In the examples youve seen so far, we havent had much control over the XML content that Access generates. In the next section, Ill look at some of the options you have when exporting XML data.

 

Setting export options

You probably noticed the More Options button at the left of the Export XML dialog box each time youve exported XML content from Access (see Figure 7-13).

Figure 7-13: The More Options button in the Export XML dialog box

This button allows you to change the way Access exports XML data. One of the options available to you is including linked tables when exporting your XML content.

Exporting linked tables

If you are exporting a table from Access, you can also include linked tables by clicking the More Options button. This will display an expanded Export XML dialog box, as shown in Figure 7-14. The Data tab shows the tables that link to the currently selected table. Simply check the additional tables that youd like to include in your XML document. Note that this option isnt available when you export queries.

Figure 7-14: The Export XML extended dialog box allows you to select linked tables.

When you check the linked table name, the export will include related records from the linked table. Clicking OK will generate the linked data in XML format, as shown in Figure 7-15. You can view the data in the resource file authorsLinked.xml .

Figure 7-15: The linked tables exported in XML format

The XML file shown in Figure 7-15 includes records from both tblAuthors and tblDocuments . The documents that relate to an author are included as child elements within the <tblAuthors> element. This allows you to generate a hierarchical representation of the data within the XML document.

In this example, Ive only shown the XML data generated from the linked tables. You could also have generated a schema and style sheet from the data. Ive included authorsLinked.xsl , authorsLinked.xsd , and authorsLinked.htm in your resources so you can see the exported content.

Another option available to you is including the content from more than one linked table. You can drill down in the Export XML dialog box, as shown in Figure 7-16. Notice that the third linked table includes the text [Lookup Data] .

Figure 7-16: Selecting all linked tables from the Data tab

Exporting data in this way doesnt work so well. Instead of linking to the existing data, Access adds the content from the third table to the bottom of the XML document. In Figure 7-17, youd have to make the link between the <categoryID> element within the <tblDocuments> element and the same element in the <tblCategories> element. Thats what the text [Lookup Data] indicates.

Figure 7-17: The XML content produced from three linked tables

Given these difficulties, its probably a good idea to stick with two linked tables and avoid including tables that show the text [Lookup Data] . Well generate linked content in exercise 3.

Exercise 3: Generating linked XML content from Access

In exercise 3, well generate an XML document that contains data from a linked table.

1. Open documents.mdb in Access 2003 if necessary.

2. Export the table tblCategories as an XML file, including the linked tblDocuments table. Save the XML file with the name categoriesLinked.xml .

3. Open the categoriesLinked.xml file in an XML or text editor so you can see the contents. Within each category, you should see a list of child document elements. You can view the completed resource file categoriesLinked.xml .

 

Applying a custom transformation

You saw earlier that Access can generate a style sheet that transforms the XML document into an HTML representation. You can also apply your own custom transformation while exporting the data. Export the data as normal, making sure you click the More Options button. Click the Transforms button on the Data tab, as shown in Figure 7-18.

Figure 7-18: The Transforms button in the Export XML dialog box

Clicking this button brings up the Export Transforms dialog box, shown in Figure 7-19.

Figure 7-19: Adding a style sheet to the Export process

Click the Add button in the Export Transforms dialog box and select an XSLT style sheet. Once selected, the name of the style sheet will appear in the dialog box. Click OK to return to the Data tab of the Export XML dialog box. Click OK and Access applies the transformation as you export the data. Figure 7-20 shows a comparison of the transformed and untransformed data.

Figure 7-20: Comparing the transformed document (left) with the exported content

Well complete an exercise so you can do the comparison yourself.

Exercise 4: Transforming XML content during export

In this exercise, well transform an XML document during the export process. The transformation will add new element names and apply sorting to the content.

1. Open documents.mdb in Access 2003 if necessary.

2. Export the table tblCategories as an XML file, including the linked tblDocuments table. Save the XML file with the name categoriesLinkedTransformed.xml .

3. Click the More Options button and then the Transforms button. Click the Add button, navigate to the file categoriesLinkedTransform.xsl , and click Add again.

4. Click the OK button twice to create the XML document. Open the file categoriesLinkedTransformed.xml in an XML or text editor. You can also see my completed file with your resources. You should see something similar to the screenshot shown in Figure 7-21. Notice that Access sorted the data during the export process.

   
   Figure 7-21: The transformed XML data from exercise 4

5. Open the file categoriesLinkedTransform.xsl in an XML or text editor to see the details of the transformation. Figure 7-22 shows the style sheet.

   
   Figure 7-22: The XSLT style sheet used in the transformation

The transformation creates new element names for each category and document. Two <xsl: sort > elements sort by both category and documentName .

 

Other export options

Access provides some other export options in the Export XML dialog box. You can bring up this dialog box by clicking the More Options button during the export process.

Earlier, we saw how you could generate an XML schema while exporting data. The Schema tab in the Export XML dialog box allows you to add primary key and index information to the schema. It also allows you to create an embedded schema instead of an external schema document. Figure 7-23 shows these options.

Figure 7-23: Options available on the Schema tab

You can see an XML document generated that includes the primary key and index information with an embedded schema in Figure 7-24. Ive saved the resource file as categoriesEmbeddedSchema.xml .

Figure 7-24: The exported XML document with an embedded schema

Youll notice that the schema includes an annotation showing information about the database. It lists two indexes within the <xsd:appinfo> element, one of which is the primary key.

You can also make changes to the way Access generates the style sheet in the Presentation tab of the Export XML dialog box. Figure 7-25 shows this tab.

Figure 7-25: The Presentation tab in the Export XML dialog box

The Presentation tab allows you to export and name a style sheet file. It lets you run the transformation either through HTML or by using a server-side page written in ASP.

Importing Data from an External File

The main use of XML in Access 2003 is in exporting data to share with external applications. However, its also possible to import data from an external source into Access.

To import XML data into Access, you have to structure it in a specific way, according to the table and field names within your database. This can be hard to figure out, so its often easiest to export data from the table or query first. That way, you can see the XML structure, including the element names and their order. By exporting, you get a template to use for your new data. Use a text or XML editor to replace the existing data with new information.

Once youve created your import XML document, you can import the data by choosing File Get External Data Import . You can see this in Figure 7-26.

Figure 7-26: Importing external XML data into Access

Navigate to the XML document that you wish to import and click the Import button. When the Import XML dialog box appears, expand the structure to see the field names as shown in Figure 7-27.

Figure 7-27: The Import XML dialog box

If you click OK now, Access will try to create a new table called tblDocuments . If you have an existing table with the same name, Access will name the new table tblDocuments1 . This might be useful as you can check that the new data is compatible with the existing data before you combine the content.

Access shows a message to let you know that it has finished the import. You can see it in Figure 7-28.

Figure 7-28: The Finished importing document message from Access

You can append the data to the existing table by clicking the Options button. Select Append Data to Existing Table(s) at the bottom of the dialog box and click OK . You can see this in Figure 7-29.

Figure 7-29: The Import XML dialog box

You can also create a new table structure without the data by choosing the Structure Only option before clicking OK in the Import XML dialog box. Again, if you choose an existing table name, Access will rename your new table.

Dealing with import errors

Access wont allow you to import XML data containing errors. If your XML document is not well formed , Access will display an error message similar to the one shown in Figure 7-30.

Figure 7-30: Access displays an error message if you try to import a document that is not well formed.

Access will also generate an error message if you try to import content that violates the data integrity rules within the database. It will store the error in a table called ImportErrors in the current database and display an error message dialog box. For example, Figure 7-31 shows the message that generates when you attempt to import a duplicate primary key value.

Figure 7-31: Access cannot import records that would cause an error.

The ImportErrors table contains the detailed error message. Ive shown this in Figure 7-32 where the table is open in Datasheet view.

Figure 7-32: A detailed error message in the ImportErrors table

Lets do an exercise where Access imports content from an XML document.

Exercise 5: Importing XML content into Access

In this exercise, well import content into Access from an external XML file.

1. Open documents.mdb in Access 2003 if necessary.

2. Open the file documentsImport.xml in a text or XML editor so you can see the data template. Feel free to change the document title but dont change the other data.

3. Import the file documentsImport.xml from your resources into Access using File Get External Data Import . Dont forget to change the file type to XML before you click the Import button.

4. In the Import XML dialog box, select tblDocuments , click the Options button, and choose Append data to Existing Table(s) . Click OK .

5. You should see a Finished import message. Click OK and open the table to check that it contains the new row.

6. Export tblCategories by right-clicking and choosing Export . Dont forget to change the file type to XML . Export the data, making sure that the Schema option is not checked.

7. Open the file in an XML or text editor. Ive called mine categoriesStructure.xml , and you can find it with your resources.

8. Well modify the content of the XML document so that it contains a single <tblCategories> element. Change the contents to create a new category, using the categoryID 6 .

9. Import the new category and append it to tblCategories , following the same approach that you used earlier in steps 3 ˆ 5.

10. Check that tblCategories contains your new data.

In this exercise, you saw how you could export a file and then modify the content to create an import document for Access. If you are exporting content from another application, you can easily create an XSLT style sheet to transform the content into the correct structure. The style sheet will need to transform the XML document, changing the element names to match those in the table.

 

Transforming content

If the XML document that you want to import doesnt match the table structure within Access, you can transform it by applying an XSLT style sheet during the import. The transformation changes the XML document from one format to another by changing the element names and their order. You can use the transformation to choose specific elements within the source XML document. As Access cant import attributes, the transformation can also change attributes to elements. Well look at this in more detail a little later on.

Once youve identified the discrepancies between the data structures, youll need to create a style sheet that carries out the transformation. Unlike the earlier transformations, this style sheet will produce another XML document. It will have to match the existing XML structure required by the database.

Once youve created the style sheet, choose File Get External Data Import and click the Transform button, as shown in Figure 7-33.

Figure 7-33: The Transform button in the Import XML dialog box

Click the Add button in the Import Transforms dialog box and navigate to the XSLT style sheet. Figure 7-34 shows the dialog box after the adding the style sheet. Click OK twice to import the transformed data.

Figure 7-34: Adding a transformation

Access displays the message shown in Figure 7-35 when it completes the import.

Figure 7-35: Access has completed the import.

If the transformation creates an XML document that Access cant import, youll see the error message shown in Figure 7-36.

Figure 7-36: Tranformation errors prevent XML data from being imported in Access.

As previously mentioned, one of the reasons that you might need to transform data during the import process is if your XML document contains attributes. Well look at importing attributes in the next section.

Using a style sheet to import attributes

Access cant deal with attributes in XML elements. If you need to import content that includes attributes, youll have to use a transformation to change the content to elements before Access can include that content within a table. You can do this as part of the import process.

In the following XML document, all of the content is stored within attributes. You can see this content in the resource file documentsImportAttributes.xml .

 <?xml version="1.0" encoding="UTF-8"?> <allBooks>   <book id="10" name="More great bike trips" authorID="4"   publishYear="2005" categoryID="5" /> </allBooks> 

To transform the data from attributes to elements, well need to apply a style sheet during the import process. This code shows the XML document structure that Access requires for imports:

 <?xml version="1.0" encoding="UTF-8"?> <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2005-06-03T06:55:34">   <tblDocuments>     <documentID>10</documentID>     <documentName>More great bike trips</documentName>     <authorID>4</authorID>     <documentPublishYear>2005</documentPublishYear>     <categoryID>5</categoryID>   </tblDocuments> </dataroot> 

We can achieve the transformation by applying an XSLT style sheet. Well work through an exercise to explore this further.

Exercise 6: Applying a transformation during import

In this exercise, well use an XSLT style sheet to transform an XML document containing attributes into a structure that Access can import.

1. Open documents.mdb in Access 2003 if necessary.

2. Import the file documentsImportAttributes.xml from your resources into Access. Click the Options button in the Import XML dialog box.

3. Add a transformation that uses the file documentsImportTransform.xsl . You can find this file in your resources. Append the data to the end of the existing table tblDocuments .

4. Open tblDocuments to check that Access has appended the data successfully.

Ive shown the contents of the XSLT style sheet here. You can also view the resource file documentsImportTransform.xsl .

 <?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" version="1.0"/>   <xsl:template match="/">     <dataroot xmlns:od="urn:schemas-microsoft-com:officedata"     generated="2005-06-03T06:00:01">       <xsl:for-each select="/allBooks/book">         <tblDocuments>           <documentID>            <xsl:value-of select="@id"/>           </documentID>           <documentName>             <xsl:value-of select="@name"/>           </documentName>           <authorID>             <xsl:value-of select="@authorID"/>           </authorID>           <documentPublishYear>             <xsl:value-of select="@publishYear"/>           </documentPublishYear>           <categoryID>             <xsl:value-of select="@categoryID"/>           </categoryID>         </tblDocuments>       </xsl:for-each>     </dataroot>   </xsl:template> </xsl:stylesheet> 

The style sheet moves through each attribute and converts it to a named element. It uses the shorthand XPath notation @ to refer to each attribute. You can find out more about XPath statements in XSLT style sheets in Chapter 3.