Applying Custom XSL Transforms to XML Data

ReportML and the .xsl files it generates handle the most common use of XSLT generating Web pages from your Access data. As mentioned earlier in the chapter, XSLT also can transform one class of XML documents to another. For example, you can apply XSLT to transform an XML data document to the format required by the Office Web Components (OWC) 11 Spreadsheet control.

Access doesn't include in the Export XML dialog the capability to specify a custom .xsl file to process exported XML documents. By adding a small amount of VBA code to your Access application, however, you can apply a transform when importing or exporting XML documents. Access 2003's Application.ExportXML and Application.ImportXML methods handle export/import operations, and the new Application.TransformXML method applies a custom transform.

Note

A macro warning message opens when the VBA code executes the Application.TransformXML method, because the transform contains VBScript code. This message appears with macro security set to Low or Medium. Click Yes to dismiss the message.

The frmXMLSS11 form of the XMLXSL23.mdb sample database in the \Seua11\Chaptr23 folder includes VBA code that uses custom XSLT in the Top100SS.xsl file to transform element-centric XML data to an XML data document for the OWC Spreadsheet control. Figure 23.34 shows frmXMLSS11 in Form view displaying data generated from a modified version of the Sales Totals by Amount query named Top100. XMLXSL23.mdb also includes frmXMLSS10 an Office XP and OWC10 version of the form. The frmXMLSS10 form substitutes a custom VBA procedure for the Application.TransformXML method.

Figure 23.34. The OWC11 Spreadsheet control displays XML data that's exported to element-centric XML and transformed to Spreadsheet namespace requirements.

Note

The ossTop100 Spreadsheet control of frmXMLSS expects to find its source Top100Data.xml, Top100SS.xsl, and Top100SS.xml files in the application folder. If you receive an error message when you open either frmXMLSS10 or frmXMLSS11, copy the three files from ...\Seua11\Chaptr23 to the project's application folder.

When you click the Regenerate Spreadsheet Data, the VBA code behind frmXMLSS transforms the data with Top100SS.xsl to the urn:schemas-microsoft-com:office:spreadsheet namespace, and recreates the Top100SS.xml document. Excel 2003 uses the same namespace, so you can export the XML data from the control to an Excel worksheet by clicking the Export to Microsoft Excel button.

Note

Providing a VBA code listing before you reach Part VII, "Programming and Converting Access Applications," might appear premature. The purpose of the listing here is to demonstrate the similarity of the VBA code to that of the VBScript and ECMAScript in the .asp and .htm files described earlier in the chapter.

Listing 23.2 is the VBA code for the command button (cmdLoadSS) Click event and the generic TransformXML subprocedure that creates to DOM documents and applies the transformNode method, as described in the earlier "Understanding the Role of Access's ReportML" and "Analyzing the Exported XML Files" sections. You can use the TransformXML subprocedure in any application for which you have the appropriate .xsl file.

Listing 23.2 VBA Code to Export and Transform an XML Data Document to the OWC Spreadsheet Namespace

 Option Compare Database Option Explicit Private Sub cmdLoadSS_Click()   Dim strTransform As String   Dim strSource As String   Dim strOutput As String   Dim strPath As String   strPath = CurrentProject.Path    'Export the XML data from the query    'Specify input, transform, and output file names    strSource = strPath & "\Top100Data.xml"    strTransform = strPath & "\Top100SS.xsl"    strOutput = strPath & "\Top100SS.xml"    'Transform the data to Office Web Control Spreadsheet format   Application.TransformXML strSource, strTransform, strTarget End Sub 

Figure 23.35 shows IE 6.0 displaying part of the XSLT in the Top100SS.xsl file, which is a derivative of the XSLT generated by Access 2002's ReportML. The urn:schemas-microsoft-com:office:spreadsheet namespace has a <Workbook><Worksheet><Table><Row><Cell><Data>...</Data></Cell></Row></Table></Worksheet></Workbook> hierarchy. <Workbook>...</Workbook> is the root element, which corresponds to the <dataroot>...</dataroot> element of Access XML data documents.

Figure 23.35. XML data that uses the ...office: spreadsheet namespace has a deeper hierarchy than Access XML data. The <Row> element is a child of the <Workbook><Worksheet> <Table> parent element.

Tip

Monitor Microsoft's Office Update Web site (http://officeupdate.microsoft.com/welcome/access.asp or choose Help, Office on the Web) for Access Add-Ins and new or updated XSLT files for transforming Access 2003 XML data documents to Microsoft and other industry-standard XML namespaces. You also can expect many third parties to offer useful XSLT files that handle the generic element-centric XML exported by Access 2003.