Typically, the ability to do your job depends on gathering data, converting that data into usable information, and sharing the information with others. The difficulty comes from the fact that much of that data is usually scattered across a wide variety of internal and external data sources and in a wide range of formats. Data can come from:

• Internal Microsoft SQL Server™ computers
• Internal AS400 servers
• Microsoft Office Excel 2003 spreadsheets
• Other external data providers, such as SAP
• Text files
• Applications with proprietary data formats

Fortunately, many of these sources support the use of XML as their data interchange format, so it is relatively easy to adapt this information for your own use. Assume that you need to periodically consolidate data from various sources to create a report for review by your managers and co-workers.

To do this using XML, you can:

1. Work with the administrators of the various data sources to export the data to a series of XML files located on a remote server.
2. Import the data from the XML source files into your SQL Server database. You could also import this data into an Access database or a local copy of a SQL Server 2000 Desktop Engine which also supports XML data imports. You could even automate this process by creating Visual Basic for Applications code in Access to implement a timer which executes the ImportXML method.
3. Use an Access project front end attached to the SQL Server to create a series of aggregation queries against the imported data.
4. Use those views to design the appropriate reports or data access pages.
5. Print the report, provide a snapshot of the report, or post a page to an intranet web site.

The key benefit of using XML in this scenario is that other than creating the queries, no other data manipulation is required to pull the data together from the various sources into a SQL Server or Access database.

Without XML, this task might involve exporting the data to a text-based file (assuming that the various data sources supported this), manipulating the data files by adding delimiters to separate the data into discrete parts, importing the data into SQL Server or Access, and then spending a considerable amount of time cleaning up the data. Using XML allows you to minimize this kind of time spent reformatting and cleaning up your data.

If you are importing and using data stored as XML, it helps to understand a little about the structure of that data and how the data is stored in the target table in the database.

What are XML elements and attributes?

Like HyperText Markup Language (HTML), XML uses element tags and attribute assignments to provide information about the data and its structure, such as font styles, data types, grouping information, information about the printer, and control properties. A typical XML element consists of a start-tag, the element data, and an end-tag. An attribute is normally contained in the element start-tag as a name-value pair and provides additional information about the element. The general form of an element tag and attribute is:

<START-TAG attribute="value">element data<END-TAG>

What goes where when importing?

When Access imports data from an XML data file, the data follows certain rules as it populates a table. Processing statements (which start with < and the ? symbol) and comments (which start with < and the ! symbol) are not imported. The table name is taken from each outer element which contains children (nested elements). Only element data is imported. Any attribute data is ignored. Field names in the table are taken from the nested element tag names. For example, for this XML data file:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot>
<EMPLOYEES>
  <LASTNAME type="client">Davolio</LASTNAME>
</EMPLOYEES>
<ADDRESS>
  <HOME>123 Anywhere Avenue</HOME>
</ADDRESS>
</dataroot>

 Note   In order to be successfully imported into Access, an XML document must be well-formed. This means that, among other things, the document must contain a document element which contains all of the other elements in the document.

For more information about the components of an XML data file, see the Microsoft Developer Network (MSDN).

For more information on how to import XML data into your Access project or database, see Access Help.

In addition to collecting and consolidating information, many times you need to share this information with other applications or with your co-workers. Access provides options in the Export XML dialog box that assist you in exporting the data from your Access project or database to an XML data file, and also with exporting the schema definition of your data and creating files that help you manage the presentation of your data.

Access Export XML Dialog Box

Exporting schema information along with your data allows other applications to determine the structure of your data and how that structure might need to be modified to meet the requirements of the receiving application. By using schema (.xsd) files, other applications can see what elements and attributes comprise your data, which elements are mandatory and which are optional, in what order they need to be displayed, and much more. In applications other than Access, different types of schemas are sometimes used, as in the case of Document Type Definition (DTD) files. However, unlike XSD files, DTDs are not written in XML syntax and are limited in their ability to describe the data structure.

Access Export XML Schema Dialog Box

The .xsd schema is written in XML syntax and can be embedded directly inside the XML data file or linked to an external schema file by selecting the appropriate option in the Export XML dialog box.

Example of schema embedded inside an XML data file

<?xml version="1.0" encoding="UTF-8"?>

  <!-- The information between the "XML" tags is the schema section-->
 <xml xmlns:od="http://www.microsoft.com/Office/Schema"
 xmlns:rs="urn:schemas-microsoft-com:rowset">
 <xsd:rs id="RowsetSchema">
  <xsd:element name="ShipperID" od:jetType="autonumber" 
   od:sqlSType="int">
   ...
  </xsd:element>>
  <xsd:element name="CompanyName" od:jetType="text"
   od:sqlSType="nvarchar">
   od:MaxLength="40">
   ...
 </xsd:Schema>
 </xml>

  <!-- The actual XML data section starts here-->
 <Shippers>
  <ShipperID>1</ShipperID>
  <CompanyName>Speedy Express</CompanyName>
  <Phone>(503) 555-9831</Phone>
   ...
 </Shippers>

Using XSL to format your data

The Export XML dialog box also provides options for you to save the format of your data in an Extensible Stylesheet Language (XSL) file. Unlike HTML, XML provides no formatting information. An XSL file provides a means of formatting your XML data by providing a document style sheet written with XML syntax.

So how is the XSL file used? The XML data file contains a tag that references the XSL file. When you open the XML data in your browser, the XSL file is processed against the data file to produce an .htm file that is displayed in the browser. As you'll see later, Microsoft Internet Explorer has a default XSL style sheet that displays XML files as a tree-like structure.

Access Export XML Presentation Dialog box

The .xsl file created from the Export XML dialog box can be used as a starting point for specifying your own formatting needs. This might include formatting your document for presentation in a spreadsheet, on a laptop, for a particular Web browser, or for use with a personal digital assistant. XSL uses a series of templates to traverse the XML data as a tree, displaying a particular group (branch) of elements based on specific conditions. HTML tags are then interspersed with the data to change the formatting. Conditional commands within the templates are used to modify the structure of the document. The following is an example of an XSL file:

<?xml version="1.0"?>
 <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl" language="vbscript">
 <xsl:template match="/">
 <HTML>
 <HEAD>
 <TITLE>
 Employees</TITLE>
 </HEAD>
 <STYLE TYPE="text/css"></STYLE>
 ...
 <xsl:for-each select="//Employees">
 <xsl:eval>AppendNodeIndex(me)</xsl:eval>
 ...

Processing instruction identifies this as an XML file
XSL template instruction
HTML tags for formatting
XSL programming instruction

In this example, the first XSL template command

<xsl:template match="/">

<xsl:for-each select="//Employees">

We will cover more details of an XSL file when we discuss transforming your data into another format later in this article.

For more information on how to export data from your Access project or database to an XML data file, see Access Help.

Previously, we discussed exporting your data (or Access object) to an XML file with the properties and characteristics it had while in Access. Suppose you wanted to "transform" your data (or object) into another format; for display in a Web browser, for example. To accomplish this, you might want to restructure the data by filtering or sorting, and by adding HTML tags to tell the browser how to display the data. You could do this manually by adding HTML tags to the .xml file and rearranging the data, which could be a rather tedious task, or you could use another type of XML-based file to do the transformation: an XSL Transformation (XSLT) file.

XSLT files (XSLT commands are a subset of XSL commands) are designed primarily for transforming one XML document into another. While this may sound limiting, XSLT is also well-suited for transforming XML to HTML or to other text-based formats.

What's the difference between XSL and XSLT? An XSL file defines the formatting and presentation of XML documents to a screen, printer, or other display device. An XSLT file actually performs the transformation.

The process of transforming a document is accomplished as follows:

1. Create an XSLT file (with an .xsl file extension just like XSL), outside Access, by using a text editor such as Microsoft Notepad. In this file, you add processing instructions to convert and restructure your data, as necessary. You need a processing instruction for each element or group (branch) of elements that you want in your output document.
2. Add a reference tag that points to the XSLT file from within the XML data file (.xml file).
3. Use an XSLT processor to apply the XSLT style sheet to the XML data file. This will produce the necessary output file (a .htm file).

There are several XSLT processors available for use including the Microsoft MSXML3 processor which can be downloaded without charge from the Microsoft Developer Network (MSDN) Web site. To run the following examples, you will need the MSXML3 processor installed on your hard disk.

How does XSLT work?

Like XSL, XSLT uses templates and instructions triggered when a particular element or branch of elements in the XML data file are encountered. Template instructions locate elements with the data file and specify what output to process. Much of the template body consists of HTML tags and text for output. Instruction commands tell the processor what part of the data to output. Instructions handing particular elements or branches of elements are placed in the order in which the data is to be output, regardless of the actual order of the elements in the XML data file. This provides the means to restructure the data to fit your needs. Conditional programming instructions allow you to filter and sort the data. The combination of these techniques provides powerful tools for transforming your XML data into virtually any format.

To get your first look at a default .xsl style sheet in action, we'll use Microsoft Internet Explorer to look at a file exported from the Access Northwind Traders sample database.

1. Open the Northwind sample database. The default location of the Northwind.mdb database is C:\Program Files\Microsoft Office\Office11\Samples, for Access 2003 — C:\Program Files\Microsoft Office\Office10\Samples, for Access 2002.
2. Export the Employees table as an XML document. For more information on exporting as XML, see Access Help.
3. Locate the Employees XML file (*.xml) and double-click the icon.
4. Assuming your default browser is Internet Explorer, the file will be displayed as a tree structure with expandable branches of elements. This tree structure is created from an XSLT file used by Internet Explorer to transform the data.

Default XML output from Internet Explorer illustrating the expanded data sections:

 <?xml version="1.0" encoding="UTF-8" ?>
 
 - <dataroot>
 
 -   <Employees>
 
   <EmployeeID>1</EmployeeID>
   
   <LastName>Davolio</LastName>
   
   <FirstName>Nancy</FirstName>
   
   <Title>Sales Representative</Title>
    
   <TitleOfCourtesy>Ms.</TitleOfCourtesy>
   
   <BirthDate>1968-12-08T00:00:00</BirthDate>
   
   <HireDate>1992-05-01T00:00:00</HireDate>
   
   <Address>507 - 20th Ave. E. Apt. 2A</Address>
   
   <City>Seattle</City>
   
   <Region>WA</Region>
   
   <PostalCode>98122</PostalCode>
   
   <Country>USA</Country>
   
   <HomePhone>(206) 555-9857</HomePhone>   ....

This is a portion of the exported Employees XML data file:

<?xml version="1.0" 
 encoding="UTF-8"?>
 
   <dataroot>
 
    <Employees>
 
      <EmployeeID>1</EmployeeID>
      
      <LastName>Davolio</LastName>
      
      <FirstName>Nancy</FirstName>
      
      <Title>Sales Representative</Title>
      
      <TitleOfCourtesy>Ms.</TitleOfCourtesy>
      
      <BirthDate>1968-12-08T00:00:00</BirthDate>
      
      <HireDate>1992-05-01T00:00:00</HireDate>
      
      <Address>507 - 20th Ave. E. Apt. 
 2A</Address>
 
      <City>Seattle</City>
      
      <Region>WA</Region>
      
      <PostalCode>98122</PostalCode>
      
      <Country>USA</Country>
      
      <HomePhone>(206) 555-9857</HomePhone>
      
      ... 
      <ReportsTo>5</ReportsTo>
    </Employees>
   </dataroot>

Now let's look at a simple XSLT file example. This file can be used to display select fields from the first record of the Employees XML data file created earlier. Later, you'll see the steps to test it yourself.

XSLT file designed to display the first record in the Employees table

<?xml version="1.0" encoding="UTF-8"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
 <HTML>
 <BODY>
 <h4>First record of the Employees table</h4>
 <p><xsl:value-of select="//EmployeeID"/></p>
 <p><xsl:value-of select="//TitleOfCourtesy"/></p>
 <p><xsl:value-of select="//FirstName"/></p>
 <p><xsl:value-of select="//LastName"/></p>
 <p><i><xsl:value-of select="//Title"/></i></p>
 <p><xsl:value-of select="//HomePhone"/></p>
 </BODY>
 </HTML>
 </xsl:template>
 </xsl:stylesheet>

First two lines contain required XML processing instructions.
First template rule tells the processor to start processing at the start of the XML data file. The "/" indicates that this rule is to be triggered as soon as the document starts to be processed.
Start of the HTML tags that we want included in our output .htm document.
HTML heading that we want to appear at the top of our output document.
Start of a series of template rules that find the element tags indicated in the select statement and output the value of those tags to our output document. The "//" in front of the values are navigating symbols that indicate to the processor (MSXML3) that it will need to go down two levels (in our Employees XML data file, this would be the Document element "dataroot", down through the "Employees" element, to the desired element "EmployeeID", for example) to reach the desired tag.
Start of the corresponding close tags. Each beginning tag must have a closing tag.

To test this file, you will need to:

• Open a text editor such as Notepad.
• Copy and paste the example file shown above into a blank Notepad document. Note that you will need to delete the blue background callouts from the file after pasting into the text editor and before running the file from the processor.
• Save the file to the same location as the Employees XML data file that you created earlier. Name the file MyTransformFile.xsl. Be sure to include the .xsl extension.
• Open the Employees XML data file with a text editor such as Notepad.
• Place your cursor at the end of the first line in the document and press Enter.
• Copy and paste the following line of text into the new blank line:

  <?xml-stylesheet type="text/xsl" href="MyTransformFile.xsl"?>

• This line tells the Employees XML file to use the style sheet file you created. Save the Employee XML data file.
• Now double-click the Employee XML data file to open it. It should open in your browser and display only specific fields in the first record from the Employees table.

Output from the first sample .xsl file:

First record of the Employees table

1

Ms.

Nancy

Davolio

Sales Representative

(206) 555-9857

Notice from the output that we limited the number of fields displayed and changed their order from the Employees table (first name then last name). We included additional information in the output display which wasn't in the original document (document title). We also added additional formatting such as the PARAGRAPH (<p>) tags and the ITALICIZED (<i>) tags to the Title element.

Now we will now look at another sample .xsl file that will display certain fields for all of the records in the Employee XML file.

XSLT file to display all of the records in the Employees table

<HTML xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0">

<BODY>
 <h4>All records of the Employees table</h4>
 <xsl:for-each select="//Employees">
 <br><xsl:value-of select="EmployeeID"/></br>
 <br><xsl:value-of select="TitleOfCourtesy"/></br>
 <br><xsl:value-of select="LastName"/></br>
 <br><xsl:value-of select="FirstName"/></br>
 </xsl:for-each>
 </BODY>

Contains the required XML processing instruction, this time combined to show that you can integrate them with HTML instructions.
Start of the HTML tags to include in the output .htm document.
First instruction, which tells the processor to find the Employees tag. Notice that this tells the processor that it will need to go down two levels to find the tag (root element and the Document element "dataroot") .
Start of a series of instructions that locate the tags displayed in the select statement and output the value of those tags to the output document.
Start of the corresponding close tags. Remember each beginning tag must have a corresponding closing tag.

To run this sample, you will need to use the same steps as with the first example. Save the XSLT file with a different name from the first.xsl file you created. Be sure to change the reference in the Employees XML file to the new .xsl file. Note that you will need to delete the blue background callouts () from the file after pasting the text into the text editor and before running the file from the processor.

Double-clicking on the Employees XML file will display the following in your browser:

First record of the Employees table

1
Ms.
Davolio
Nancy
2
Dr.
Fuller
Andrew
3
Ms.
Leverling
Janet
...
9
Ms.
Dodsworth
Anne

In this output, we changed the spacing of the element data by using BREAK <br> HTML tags instead of PARAGRAPH <p> tags between the elements. We chose to output fewer elements than we did in the first example. We also reversed the order of the first name and the last name. These are just a few of the techniques available to transform your data. Hopefully, you will continue to experiment with these and other techniques to discover how XSL and XSLT can be used to create the types of data files that you need.

This article has introduced some of the ways that XML data, XML-based files such as XSLT and XSL, and Access can be used together to facilitate moving and shaping information. Using these ideas as a starting point, you should be able to extend these examples to work in your own applications and tasks. For more detailed information on any of the concepts presented in this article, search the Microsoft Developers Network (MSDN ) Web site.