About XML data and Access

Moving information across the Internet and between software applications has always been difficult due to differences in data formats and proprietary structures. HTML, while well suited for providing text and image display information for Web browsers, is limited in its ability to define data and data structures. HTML describes how a Web page should look, whereas XML defines the data and describes how the data should be structured. XML is a data interchange format in that it allows you to exchange data between dissimilar systems or applications. XML separates the data from the presentation so that the same XML data can be presented in multiple ways by using different presentation files.

XML is a standards-based protocol that is governed by the World Wide Web Consortium (W3C). The XML protocol is a set of rules, guidelines, and conventions for designing data formats and structures, in a way that produces files that are easy to generate and easily read by different computers and applications. In addition, XML defined structures are unambiguous; that is, they are self-describing and platform-independent. Like HTML, XML makes use of tags and attributes, but while HTML specifies what each tag and attribute means (and thus how the data between them will look in a browser), XML uses the tags only to delimit pieces of data, and leaves the interpretation of the data completely to the application that reads it. Additional information about XML can be found on the MSDN Web site.

Access provides choices for using data from many external sources. XML makes it easier to transform the data from almost any external application for use by Access. You can:

• Import XML data to a Jet, Microsoft SQL Server, or Microsoft SQL Server 2000 Desktop Engine (formerly called MSDE) database.
• Import XML schema data to a Jet, SQL Server, or Microsoft SQL Server 2000 Desktop Engine database.
• Use an Extensible Stylesheet Language Transformation (XSLT) file to transform the data into an XML format that Access supports.

Note  Importing XML into SQL Server or Microsoft SQL Server 2000 Desktop Engine database through an Access project (ADP) requires that Microsoft SQL Server 2000 Desktop Engine is installed locally.

You can use the Import command (point to Get External Data on the File menu) to import XML data files into Access. This command displays the Import dialog box so that you can select an XML document as well as a schema, which describes the structure of the data. You can only import a single document at a time into Access. The data must be in a format that Access recognizes, either in a native format or through the use of a schema. Note that when importing XML data, you cannot choose a subset of the XML document; the entire file has to be imported.

You can transform any XML data into a format that Access supports by selecting a transform in the Import XML dialog box. You can also specify whether to overwrite any existing tables or append to existing data.

You use XML schemas to describe the structure of data in a common format that customers, other Web browsers, and any number of XML-enabled software programs can recognize. Specifically, schemas define the rules of an XML data document, including element names and data types, which elements can appear in combination, and which attributes are available for each element. Schemas provide a model for an XML data document which defines the arrangement of tags and text within all documents referencing the schema. Access supports the XML Schema standard (XSD). XSD is an approved World Wide Web Consortium (W3C) standard designed as a basic infrastructure for describing the type and structure of XML documents.

By using a schema, you can ensure that any XML document that is used to import data into Access or export from Access to another format contains specific data and conforms to a defined structure. You can also provide the schema to other businesses and applications so that they know how they should structure any data they provide to you and they, in turn, can provide their schema to you.

XML syntax (the tags and their placement in a document) defines and describes the data in an XML document but doesn't indicate how the data should be displayed. In earlier applications and in some current uses, Cascading Style Sheets (CSS) were used to tell the browser how to display the XML data. However, a CSS isn't always a good choice because they are written in a specialized language which means that the developer has to use another language in order to write, modify, or understand the structure of the style sheet. A CSS file merely allows you to specify the formatting of each XML element without much control over the output.

On the other hand, an Extensible Stylesheet Language (XSL) style sheet is considerably more flexible than a CSS file. It allows you to precisely select the data that will be displayed, to specify the order or arrangement of the data, and to modify or add information. Additionally, it is written in a style similar to an XML document using a combination of XML-like tags and HTML to create a template for a specific style of output. Additional information about XSL style sheets can be found on the MSDN Web site.

Note that you are not required to link either a CSS file or an XSL style sheet to an XML document in order for Microsoft Internet Explorer 5.01 with Service Pack 2 (SP2) or later to display the document. Internet Explorer has a default, built-in style sheet that displays the XML source as a collapsible/expandable tree.

You can use style sheets to insure that the XML-based Web pages on your intranet or Website are consistent and present a uniform appearance without having to add HTML to each page.

XSL for Transformation (XSLT) is a specification that is recommended by the World Wide Web Consortium (W3C), and supported by Access. XSLT is a an XML-based language that allows one XML document to be mapped, or transformed, into another XML document. This provides a way of transforming an XML document's presentation information from a source format to a target format and back again. Typically, a developer creates an XSL transformation file that, when applied to an XML document during export, interprets or transforms the XML data into a presentation format that can be recognized by another application, such as Service Advertising Protocol (SAP) or a custom purchase order format.

XSLT has many of the constructs (structures and commands) found in other programming languages which allow the developer to use variables, loops and iterations, and conditional statements. This gives the developer considerable control over the output of the XML data. Additional information about XSLT can be found on the MSDN Web site.

Exporting data and database objects to an XML file is a convenient way to move and store your information in a format that can readily be used across the Web. In Access, you can export the data, the schema (data structure), or both, to XML files. You can:

• Export data to an XML file and, optionally, use an XSLT to transform the data to another format.
• Export the data schema using XML Schema standard (XSD).
• Export the data behind forms and reports to an XML file.

You can also transform the data to another presentation format using an Extensible Style Language (XSL) file during the export process. You can export tables, queries, and the data behind forms or reports from a Microsoft Access database (.mdb) as well as tables, queries, stored produces, functions, and the data behind forms and reports from a Microsoft Access project (.adp).

You can export a database object as an XML document in several ways:

• You can export just the data from a table, query, datasheet, form, or report into an XML file. This data is saved to a file named <filename>.xml.

  Note  When you export a table to an XML document, you can also export related tables. For example, if you export a table of Customers Orders, you can also choose to export a related Orders Details table and Customers table into the same file.

• You can export just the schema (data structure) of a table, query, datasheet, form, or report to an XML schema file. An XML schema file is a formal specification of the rules for an XML document, providing a series of element names, as well as which elements are allowed in the document and in what combinations. If you select to save the schema as XSD, the file is saved as <filename>.xsd.
• In addition, when you choose to save the data as XML, you can specify that the data be transformed to a custom display format by using an existing .xsl file. If no .xsl file is specified, the data is saved in standard XML format. Note that if no data is selected for export then a presentation format is also unavailable. The file is saved as <filename>.xsl.
• Through the Access object model, you can also can save the presentation attributes of a table, query, datasheet, form, or report into a file that describes the presentation and connection information. For forms and reports, this file is saved in an XML-based language called ReportML which provides presentation data as well as a data model for creating a data access page. For tables, queries/views, and datasheets, the presentation file is a spreadsheet-like template. This file is saved as <filename>_report.xml.

ReportML is a "language" developed by Microsoft and specific to Access which can be used to describe Access database objects in XML. The ReportML language is made up of a set of tags that describe a form, report or data access page's properties, events, and attributes. When you export data from Access to an XML file, you choose to save the structure of a form or report into a ReportML format. The ReportML file can be used to convert the saved data into a data access page.

Any XML document produced by Access is well-formed, which means that it conforms to the basic rules of XML. That is:

• Each XML document must have a unique root element (an element encompassing the entire document).
• All start and end tags match. XML tags are case-sensitive.
• For each start tag, there is a corresponding end tag. Empty elements can be denoted by a special shorthand tag. Again, XML tags are case-sensitive.

  Note  Access ReportML will not write out any empty tags.

• Elements do not overlap. In other words, start and end tag must be properly nested within other elements.
• Certain reserve characters are part of the XML syntax and will not be interpreted as themselves if used in the data portion of an element. You need to substitute a special character sequence (called an "entity" by XML) as follows:

  Character	Entity
  <	&lt;
  &	&amp;
  >	&gt;
  "	&quot;
  '	&apos;

The following is an example of a well-formed XML document:

<?xml version="1.0"?>
<ORDER>
 <CUSTOMER>Nancy Davolio</CUSTOMER>
 <PRODUCT>
 <ITEM>Mom&apos;s Boston Crab Meat</ITEM>
 <PRICE>$10.00</PRICE>
 <QUANTITY>1 Bottle</QUANTITY>
 </PRODUCT>
</ORDER>
				

where:

<?xml version="1.0"?> is a declaration that states that this is an XML document and gives the version number. The declaration optional but is recommended in any XML document.

<ORDER> is the root element which encompasses the entire document.

<CUSTOMER> is a start tag and </CUSTOMER> is an end tag which together describe an element of data, in this case, the customer's name.

Notice that each tag set, such as <CUSTOMER></CUSTOMER>, has both start and end tags and is case sensitive, and that the tag sets are properly nested within each other. Also notice the entity &apos; which will be transformed to an apostrophe (') when the data is imported by the receiving application. The apostrophe has a special purpose in an XML document and can be misinterpreted if used directly in the text. The converted data will be displayed as Mom's Boston Crab Meat.

White space can be used throughout the document to enhance readability.

If a schema is specified while exporting from Access, then the XML documents created are considered to be valid XML document. This means that in addition to being well-formed, the documents conform to a defined schema.