Exporting Tables and Queries to XML and HTML

Access 97 and later can export tables and queries directly to simple, semi-formatted HTML tables. You have the choice of exporting static HTML pages or using Active Server Pages (ASP) to base the page content on a query that executes against a Jet or SQL Server database. The format of static pages is fixed, but you can alter the formatting of ASP-generated pages by editing HTML BORDER, COLOR, BGCOLOR, BORDERCOLOR, and other table attribute values in the .asp file.

To review conventional HTML and ASP export procedures, see "Exporting Data to Web Servers," p. 316.

Access 2003's XML table and query export feature generates a plain-vanilla, static HTML table. Fortunately, you can dress up the table by editing the XSL code that transforms the XML data to HTML. (Why the Access XML developers didn't emulate with XSL the more attractive conventional HTML export format is a mystery.) The later "Reformatting HTML Tables and Adding Page Elements" section shows you how to alter the style sheet to format the table to your liking.

To generate a dynamic (live) HTML table from an SQL Server database, see "Using SQL Server 2000's HTTP Query Features," p. 976.

Do the following to create an HTML page by exporting a table or query as XML:

1. Right-click the table or query to export, and choose Export to open the Export ObjectType 'ObjectName' To dialog. This example uses vwUnion, which is a modified version of NorthwindCS.adp's Customers and Suppliers view.

   Note

   

   The XMLXSL23.mdb sample database in the \Seua11\Chaptr23 folder of the accompanying CD-ROM includes Jet versions of vwUnion and the other tables and queries for the examples of this chapter. To use SQL Server, which is recommended as the data source for all production XML and Web applications, upsize XMLXSL23.mdb to an SQL Server database (XMLXSL23SQL) and an Access project (XMLXSL23CS.adp).

   To review the upsizing process, see "Upsizing with the Trial and Error Approach," p. 889.

   
   

2. Scroll to the end of the Save As Type list, and select XML (*.xml). Click Export to open the Export XML dialog.

3. Mark the Presentation of Your Data (XSL) to add an XSL style sheet (ObjectName.xsl) to the XML data (ObjectName.xml) and schema (ObjectName. xsd) files in the current folder (see Figure 23.2). The default settings generate separate data and schema files.

   Figure 23.2. The basic version of the Export XML dialog generates the selected XML file types in the same folder as the .mdb or .adp file. Mark the Presentation of Your Data (XSL) check box to create .xsl and .htm files.

   

4. Click OK to start the export process, which takes only a few seconds for the small query result set generated by vwUnion. In addition to the three files mentioned in step 3, specifying a style sheet generates an ObjectName.htm file.

5. Open Explorer, navigate to the folder containing the .mdb or .adp, and double-click the ObjectName.htm file ...\Office11\Samples\vwUnion.htm for this example to open the page in IE 6.0+ (see Figure 23.3).

Figure 23.3. The HTML table created from the .xml,.xsl, and .htm files won't win any design awards.

Tip

If your goal is simply to deliver the output of a table or query to a single static Web page, use conventional HTML or ASP export. Exporting static data as XML is overkill in this case. The example of the preceding section is the starting point for gaining an understanding how .xml, .xsd, and .xsl files interact to create an .htm file.

Analyzing the Exported XML Schema and Data

As mentioned earlier in the chapter, the primary purpose of XML is to enable interchange of information between multiple applications that run on different operating systems. The capability of XML data to pass through corporate or personal firewalls lets you exchange selected data from your Jet or SQL Server databases with other organizations over the Internet. A major benefit of extracting the data as an XML file is that allowing outsiders to connect to your database isn't required to obtain needed information.

By default, XML data is Unicode (UTF-8) text; the data document isn't aware of the data type of the table fields or query columns that generate the text. If you create an XML data document from a table or query that includes fields of the Jet DateTime or SQL Server datetime data type, such as the Northwind or NorthwindCS Orders table, the date elements appear in XSD dateTime data type format as

 <OrderDate>1996-07-04T00:00:00</OrderDate> <RequiredDate>1996-08-01T00:00:00</RequiredDate> <ShippedDate>1996-07-16T00:00:00</ShippedDate> 

XSD's dateTime text format is well suited to sorting, but isn't easily readable in a table. Thus the recipient's XSL code must have access to the schema of the table or query to determine how to interpret and, if necessary, transform the data. The XML schema, which you can embed in the XML data file or save as an independent .xsd file, supplies the field data type and other field or column properties. Any application such as Access 2003 that supports XSD can either import the data directly to a table or use XSLT to transform the schema attributes, such as sqlSType="varchar", to name-value pairs for the target RDBMS, such as Oracle or IBM DB2.

Tip

Don't embed the schema in the .xml data document you send to others. The XML applications used by your recipients might not be able to handle embedded schema. Send the .xsd schema file with the first transmission of the .xml document, and then send updates to the data as individual .xml files.

The vwUnion.xsd Schema File

Listing 23.1 shows the content of the vwOrders.xsd file you exported in the preceding section with indenting applied for easier reading. The schema defines the XML data as a complexType that contains a sequence of six simpleType column elements. Column elements that may be empty have a minOccurs="0" attribute. The combination of the od:sqlSType="varchar" attribute and <xsd:maxLength value="15"/> element, for example, define a varchar(15) data type. Each named element of the associated XML data file has a corresponding <xsd:element name="name">... </xsd:element> entry in the XSD schema file.

Listing 23.1 The XML Schema for the vwUnion View Contained in vwUnion.xsd0

 <?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:choice maxOccurs="unbounded">                  <xsd:element ref="vwUnion"/>            </xsd:choice>       </xsd:complexType>     </xsd:element>     <xsd:element name="vwUnion">           <xsd:annotation>                <xsd:appinfo/>           </xsd:annotation>           <xsd:complexType>                <xsd:sequence>                     <xsd:element name="Country" minOccurs="0"                             od:jetType="text" od:sqlSType="varchar">                          <xsd:simpleType>                              <xsd:restriction base="xsd:string">                                    <xsd:maxLength value="15"/> </xsd:restriction>                     </xsd:simpleType>                </xsd:element>         <xsd:element name="City" minOccurs="0"              od:jetType="text" od:sqlSType="varchar">          <xsd:simpleType>               <xsd:restriction base="xsd:string">                    <xsd:maxLength value="15"/>               </xsd:restriction>          </xsd:simpleType>    </xsd:element>    <xsd:element name="Company" od:jetType="text"            od:sqlSType="varchar">         <xsd:simpleType>              <xsd:restriction base="xsd:string">                   <xsd:maxLength value="40"/>              </xsd:restriction>         </xsd:simpleType>    </xsd:element>    <xsd:element name="Contact" minOccurs="0"             od:jetType="text" od:sqlSType="varchar">        <xsd:simpleType>               <xsd:restriction base="xsd:string">                    <xsd:maxLength value="30"/>               </xsd:restriction>         </xsd:simpleType>    </xsd:element>    <xsd:element name="Relationship" od:jetType="text"            od:sqlSType="varchar"          <xsd:simpleType>               <xsd:restriction base="xsd:string">                    <xsd:maxLength value="8"/>               </xsd:restriction>           </xsd:simpleType>        </xsd:element>      </xsd:sequence>    </xsd:complexType>   </xsd:element> </xsd:schema> 

The schema for a table with a variety of data types is more interesting than that for a view having only text/ varchar data types. Figure 23.4 shows IE 6.0 displaying the xsd:appinfo node of the schema for the Orders table. This node specifies the properties of the indexes on the Orders table; the node is empty in the code of listing 23.1 because the view isn't indexed. (You need SQL Server 2000 Enterprise Edition to generate indexed views.) Export the Orders table to XML and specify a schema file, open the Orders.xsd file in IE 5+, and scroll to the xsd:element nodes for fields to see how SQL Server data types map to the predefined xsd data types.

The vwUnion.xml Document File and Its CDATA Sections

Figure 23.5 shows IE 6.0 displaying the vwUnion.xml file's XML dataroot root element, its namespace attributes, and the elements of the first two rows. The XML code for all rows of the view has a consistent structure.

Figure 23.4. The XSD schema for NortwindCS's Orders table includes the properties of the table's indexes.

Figure 23.5. IE 6.0's built-in style sheet improves readability of XML data documents by indenting the sub-nodes (sub-trees). If the XML document is malformed, an "XML page cannot be displayed" message occurs at the location of the error.

If you choose View, Source to open the file in Notepad, scroll to the second row for Australia, you see the following line:

 <Company><![CDATA[G'day, Mate]]></Company> 

There are 17 occurrences of CDATA in the file: 16 element values include an apostrophe (') and one has an ampers and (&).

A CDATA (character data) section permits inclusion of characters that XML interprets as delimiters or large blocks of text that contain special characters or formatting that doesn't conform to XML syntax rules. For example, XML interprets the apostrophe in G'day as a value delimiter. The ![CDATA[free text]] format is required to ensure that the XML parser doesn't inadvertently interpret ordinary data as CDATA. A common use for CDATA sections is to contain binary data for bitmaps and scripting code in XSLT.

Note

Microsoft uses CDATA sections to handle illegal characters, but it's more common to use conventional HTML literals, such as &apos; for apostrophe and &amp; for ampersand. Substituting <Company>G&apos;day, Mate></Company> for <Company><![CDATA[G'day, Mate]]></Company> works just as well.

Spelunking the vwUnion_report.xml and vwUnion.xsl Files

If you apply the Registry key described in the tip at the end of the earlier "Understanding the Role of Access's ReportML" section, the ReportML data file is preserved when you export an Access object. Figure 23.6 shows the first few lines of the vwUnion_report.xml file. ObjectName_report.xml files exemplify XML's ability to represent software objects by a set of custom tags and element values. ReportML has several hundred tag definitions for the properties and events of all Access objects. vwUnion_report.xml uses only the ReportML elements that relate to table or query properties.

Figure 23.6. If you save the ObjectName_report.xml file, you can read every property value of the Datasheet view of the table or query. It's easy to translate most of the tag names to datasheet properties set in the Access UI.

The RPT2HTML4.xsl transform, which generates the vwUnion.xsl stylesheet from vwUnion_report.xml, disregards the DATASHEET property elements; these elements emulate the behavior of a datasheet when you save tables or queries as DAP. Following are the important lines in vwUnion_report.xml:

 <CONNECTION-STRING>       PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;       PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=XMLXSL32SQL;       DATA SOURCE=(local) </CONNECTION-STRING> <DATA-MODEL> <ROW-SOURCE  type="view">      <COMMAND-TEXT>         <![CDATA[SELECT * FROM "vwUnion"]]>      </COMMAND-TEXT>      <FIELD  datatype="200" size="15"/>      <FIELD  datatype="200" size="15"/>      <FIELD  datatype="200" size="40"/>      <FIELD  datatype="200" size="30"/>      <FIELD  datatype="200" size="8"/>   </ROW-SOURCE> </DATA-MODEL> 

AccessWeb.dll uses the <CONNECTION-STRING>, <ROW-SOURCE>, and <COMMAND-TEXT> values to connect to the XMLXSL32SQL database, retrieve a Recordset from the vwUnion view, save it as an XML data document, and add the field names and data types as attributes of <FIELD> elements. The datatype="200" attribute specifies a varchar field with its length having the value of the size attribute.

The vwUnion.xsl file performs the translation from XML to HTML in accordance with rules specified with a template. Figure 23.7 shows the first few lines of vwUnion.xsl. The <xsl:template match="/'>line defines the beginning of the body of the template. Literal values such as <HTML> and <HEAD> export directly to the .htm file. XSL elements such as <xsl:for-each select="dataroot/vwUnion"> are template processing rules expressed in an XSLT sub-language called XML Path Language (XPath). The W3C issued its November 1999 recommendation for XPath 1.0 (http://www.w3.org/TR/xpath) in conjunction with the XSLT 1.0 recommendation. Coverage of XSLT and XPath coding techniques is beyond the scope of this book.

Figure 23.7. The vwUnion.xsl style sheet starts with the declaration of a template for generating HTML. A collection of VBScript functions that return values to XPath expressions are located in a CDATA section at the end of the file.

Tip

If you're interested in learning more about XSLT, Microsoft offers a comprehensive XSLT Developer's Guide. Search for "XSLT Developer's Guide" at http://msdn.microsoft.com/ to find the latest version.