Chapter 23: Using XML
Today’s modern companies are increasing productivity and cutting costs by finding ways to share more and more information online. When data can be shared in a universal format, it doesn’t matter whether an employee is down the hall, across the street, or thousands of miles away. The online sharing of information also makes it easier for companies to expand into global markets. Customers half a world away can explore a company’s products and services and place orders online. Companies can tap into vendors worldwide to find the best products at the best price.
The World Wide Web has certainly been an enabling technology for increasing productivity and expanding markets. As explained in Chapter 21, “Publishing Data on the Web,” the Web works because of the universal acceptance of protocol and language standards. Hypertext Markup Language (HTML) enables a Web page to be displayed on any computer and in any browser anywhere in the world. As an adjunct to HTML, Extensible Markup Language (XML) defines a standard and universal way to share data files or documents. Microsoft Windows SharePoint Services leverage both these technologies to provide an enhanced Web-based data and information sharing mechanism to help companies increase productivity.
This chapter explores XML in more detail and shows you how you can take advantage of these technologies to share information more readily from your Microsoft Office Access 2007 applications.
| Note | |
Exploring XML
The current XML standard is based on an ISO standard, but the most commonly used version is the one maintained and published by W3C (World Wide Web Consortium). Because a file in XML format contains not only the data but also a description of the structure of the data, XML-enabled receiving systems know exactly how to process the data from the information included in the file.
An XML document can contain data from a single table or from an entire database. An XML document can also have supporting files that describe details about the table schema (for example, field properties and indexes) or that describe how the recipient should lay out (format) the data for display (for example, fonts and column sizes).
Like HTML, XML uses tags to identify descriptive elements. Examples include the name of a table or the name of a field in an XML data file, the names of table properties or index properties in an XML schema file, and the size and color of a border or the name of a style sheet template in an XML layout file. However, where most browsers are forgiving of errors in HTML, such as a missing end tag in a table row, most software that can process XML insists that the tags in an XML file be very precise and follow strict rules. An XML document or set of documents that contain precise XML are said to be well formed.
Well-Formed XML
Although you will create most XML documents using a program such as Office Access 2007 that always creates well-formed XML, you might occasionally need to view and edit XML files that you receive from outside sources. You should understand the following rules that apply to well-formed XML:
-
Each XML document must have a unique root element that surrounds the entire document.
-
Any start tag must have an end tag. Unlike HTML that supports stand-alone tags (such as <br>), XML requires all tags to have explicit ends. However, some tags within XML are self-contained. For example, you code an object definition tag within a schema file like this (the /> characters at the end of the string define the end of the tag):
<od:object-type attributes />
-
Tags cannot overlap. For example, you cannot start the definition of one table field and then start the definition of a second table field without ending the first one.
-
When you need to include certain characters that are reserved for XML syntax (such as <, &, >,",') within the data portion of an element, you must use a substitute character sequence. For example, you indicate a single quote within data using the special sequence &apos.
-
All tags in XML are case-sensitive. For example, </tblfacilities> is an invalid end tag for the begin tag <TBLFacilities>.
As you examine the XML examples in this chapter, you should not encounter any XML that is not well formed.
Understanding XML File Types
XML documents can be made up of a single file if necessary. However, when you want to send more than the table name, field names, and data content, you can generate additional files that help the recipient understand data properties and format the data as you intended. The five types of files that can make up a set of XML documents about one table or group of tables are as follows:
-
Data document (.xml) contains the names of tables and fields and the data in the fields.
-
Schema document (.xsd) contains additional information about the properties of the tables (such as indexes defined on the table) and properties of the fields (such as data type or length).
-
Presentation (layout) document (.xsl) specifies the layout of the data, including fonts and column and row spacing.
-
Presentation layout extension (.xsx) document specifies additional properties used by the designer.
-
Web package (.htm) is a version of the information contained in the data, schema, and presentation documents compiled into HTML format ready for display in a browser.
When you create XML documents to display on your own Web site, you most likely will use all five file types to completely describe the data and format it for presentation. When you are sending a data file to another organization or business application, you usually send only the data and schema documents-the essential information that the recipient needs to understand your data.
| Note | Although you can embed schema and presentation information inside a data document, you should normally send the information as separate files. Not all applications that can process XML can handle a combined file that contains the data and the schema or the data and the schema and the presentation specification. In general, it’s a good idea to keep the data values, the data definition, and the layout specifications separate. |
One of the best ways to understand XML files is to study some examples. So, let’s look at the files Access 2007 creates when you ask it to export a small table such as the tblFacilities table in the Housing Reservations application as XML. You can learn how to create these documents from Access in “Exporting Access Tables and Queries” on page 1246.
The XML Data Document (.xml)
The data document contains very basic information about your table and the fields within the table as well as the data from the table. The data document for the tblFacilities table (tblFacilities.xml) is as follows:
<?xml version="1.0" encoding="UTF-8"?> <dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="tblFacilities.xsd" generated="2007-01-21T12:56:27"> <tblFacilities> <FacilityID>1</FacilityID> <FacilityName>Main Campus Housing A</FacilityName> <FacilityAddress>2345 Main Street</FacilityAddress> <FacilityCity>Chicago</FacilityCity> <FacilityStateOrProvince>IL</FacilityStateOrProvince> <FacilityPostalCode>60637</FacilityPostalCode> </tblFacilities> <tblFacilities> <FacilityID>2</FacilityID> <FacilityName>Main Campus Housing B</FacilityName> <FacilityAddress>2348 Main Street</FacilityAddress> <FacilityCity>Chicago</FacilityCity> <FacilityStateOrProvince>IL</FacilityStateOrProvince> <FacilityPostalCode>60637</FacilityPostalCode> </tblFacilities> <tblFacilities> <FacilityID>3</FacilityID> <FacilityName>South Campus Housing C</FacilityName> <FacilityAddress>4567 Central Ave.</FacilityAddress> <FacilityCity>Chicago</FacilityCity> <FacilityStateOrProvince>IL</FacilityStateOrProvince> <FacilityPostalCode>60637</FacilityPostalCode> </tblFacilities> <tblFacilities> <FacilityID>4</FacilityID> <FacilityName>North Satellite Housing D</FacilityName> <FacilityAddress>5678 N. Willow Drive</FacilityAddress> <FacilityCity>Chicago</FacilityCity> <FacilityStateOrProvince>IL</FacilityStateOrProvince> <FacilityPostalCode>60636</FacilityPostalCode> </tblFacilities> </dataroot>
The first line is a comment tag that notes the version of the XML standard that Access used to generate this file and states that the characters in the file comply with an 8-bit character-set standard. The next line starts the required root element of the document and identifies that the schema definition can be found in the file tblFacilities.xsd. The remaining lines, up to the </dataroot> end tag, identify the four rows in the table and the six fields within each row, including the data content of each field. Note that each row starts with a <tblFacilities> tag and ends with a </tblFacilities> tag. Likewise, each field begins and ends with a tag that names the field, and the data contents of each field appears between the begin and end field tags.
You can see that this file primarily contains information about the data contents. Except for the implied sequence of fields in each row and the sequence of rows within the table, no information about the definition of the table or the fields is in this file. Although this example file contains the data from only one table, it is possible to include the data from multiple tables in one XML file.
The Schema File (.xsd)
To find the structural definition of the table and fields, you must look in the companion schema file. Understanding how to read a schema file can be useful if you attempt to import an XML file sent to you but you don’t seem to be getting the results you expect. The beginning of the schema file for the tblFacilities table (tblFacilites.xsd) is as follows:
<?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:sequence> <xsd:element ref="tblFacilities" minOccurs="0" maxOccurs="unbounded"/> </xsd:sequence> <xsd:attribute name="generated" type="xsd:dateTime"/> </xsd:complexType> </xsd:element> <xsd:element name="tblFacilities"> <xsd:annotation> <xsd:appinfo> <od:index index-name="FacilityPostalCode" index-key="FacilityPostalCode " primary="no" unique="no" clustered="no" order="asc"/> <od:index index-name="PrimaryKey" index-key="FacilityID " primary="yes" unique="yes" clustered="no" order="asc"/> <od:tableProperty name="Orientation" type="2" value="0"/> <od:tableProperty name="OrderByOn" type="1" value="0"/> <od:tableProperty name="NameMap" type="11" value=" CswOVQAAAAByioucQ0Z3T40beJpq7gFyAAAAAPb4bxHZceJAAAAAAAAAAAB0AGIA bABGAGEAYwBpAGwAaQB0AGkAZQBzAAAAAAAAAJ8VD08qACpMs5fBc7lh6boHAAAA coqLnENGd0+NG3iaau4BckYAYQBjAGkAbABpAHQAeQBJAEQAAAAAAAAAXutJYWBV kEKh+5avJU3lowcAAAByioucQ0Z3T40beJpq7gFyRgBhAGMAaQBsAGkAdAB5AE4A YQBtAGUAAAAAAAAAmf/iHgqvp0KuAX5jWsirtgcAAAByioucQ0Z3T40beJpq7gFy RgBhAGMAaQBsAGkAdAB5AEEAZABkAHIAZQBzAHMAAAAAAAAAbWoQcZlOwEa/hQ5v XmRRvQcAAAByioucQ0Z3T40beJpq7gFyRgBhAGMAaQBsAGkAdAB5AEMAaQB0AHkA AAAAAAAA+ri0bFFbYUKAk99BNF1kGAcAAAByioucQ0Z3T40beJpq7gFyRgBhAGMA aQBsAGkAdAB5AFMAdABhAHQAZQBPAHIAUAByAG8AdgBpAG4AYwBlAAAAAAAAAOik G2+R0v9Ij2tojSMOwTUAAAAAEeO/9Ndx4kAAAAAAAAAAAHQAbABrAHAAWgBpAHAA cwAAAAAAAACkC3HuUea3S7cpPZBX0k4VBwAAAHKKi5xDRndPjRt4mmruAXJGAGEA YwBpAGwAaQB0AHkAUABvAHMAdABhAGwAQwBvAGQAZQAAAA== "/> <od:tableProperty name="DefaultView" type="2" value="2"/> <od:tableProperty name="Description" type="10" value="Table for Housing Facility records"/> <od:tableProperty name="SubdatasheetName" type="10" value="[None]"/> <od:tableProperty name="GUID" type="9" value="coqLnENGd0+NG3iaau4Bcg=="/> <od:tableProperty name="Filter" type="12" value="((tblFacilities.FacilityID=1))"/> </xsd:appinfo> </xsd:annotation> <xsd:complexType> <xsd:sequence>
The first line is a comment like the one found in the companion XML data file. The second line defines the beginning of the root element-the schema. The next eight lines, beginning with <xsd:element name="dataroot"> and ending with </xsd:element>, link this schema to the data root object defined in the tblFacilities XML file.
The tag <xsd:element name="tblFacilities"> begins the definition of the table. The information following the <xsd:appinfo> tag defines application-specific information about the structure of the table-in this case, the two indexes defined on the table. Notice that even though a desktop database (.accdb) doesn’t use a clustered property, the schema definition includes this property for compatibility with SQL Server.
Following the heading information, you can find segments that define each of the six fields in the tblFacilities table. The definition of the first field, FacilityID, is as follows:
<xsd:element name="FacilityID" minOccurs="0" od:jetType="longinteger" od:sqlSType="int" type="xsd:int"> <xsd:annotation> <xsd:appinfo> <od:fieldProperty name="Required" type="1" value="0"/> <od:fieldProperty name="ColumnWidth" type="3" value="-1"/> <od:fieldProperty name="ColumnOrder" type="3" value="1"/> <od:fieldProperty name="ColumnHidden" type="1" value="0"/> <od:fieldProperty name="Description" type="10" value="Unique Facility ID"/> <od:fieldProperty name="DecimalPlaces" type="2" value="255"/> <od:fieldProperty name="DisplayControl" type="3" value="109"/> <od:fieldProperty name="Caption" type="12" value="ID"/> <od:fieldProperty name="GUID" type="9" value="nxUPTyoAKkyzl8FzuWHpug== "/> </xsd:appinfo> </xsd:annotation> </xsd:element>
The tag beginning with <xsd:element name-"FacilityID" starts the definition of the FacilityID field. The tag specifies a data type for both the Access desktop database engine (notice the reference is to the Microsoft JET database engine) as well as for SQL Server.
The remaining five fields are all text fields. The start tag for each field defines the JET data type (text) and the SQL Server data type (nvarchar) inside the tag. Each field start tag is then followed by tags that define the simple data type as a string as well as restrictions on the maximum length of each field. The last several lines in the schema definition are end tags that close up the last field, the sequence of fields started just before the first field definition, the complex type tag just before that, the element tag that started the table definition, and finally the end tag for the entire schema.
You can see that it’s not too difficult to figure out what the schema is describing as long as you can sort out the pairs of begin and end tags. However, you probably wouldn’t want to attempt to build such a schema file from scratch.
The Presentation (Layout) Document (.xsl)
As noted earlier, you can optionally include a presentation document (also called a style sheet) to describe how the table defined by the .xsd file and the data within the file included in the .xml file should be displayed. If you ask Access to also create a presentation document (tblFacilities.xsl) for the tblFacilities table, its contents will be as follows:
<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:fx="#fx-functions" exclude-result-prefixes="msxsl fx"> <xsl:output method="html" version="4.0" indent="yes" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/> <xsl:template match="//dataroot" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <html> <head> <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=UTF-8"/> <title>tblFacilities</title> <style type="text/css"> </style> </head> <body link="#0c0000" vlink="#050000"> <table border="1" bgcolor="#ffffff" cellspacing="0" cellpadding="0" > <colgroup> <col style="TEXT-ALIGN: right; WIDTH: 0.9375in"/> <col style="WIDTH: 0.9375in"/> <col style="WIDTH: 0.9375in"/> <col style="WIDTH: 0.9375in"/> <col style="WIDTH: 0.6979in"/> <col style="WIDTH: 0.9375in"/> </colgroup> <tbody> <tr> <td> <div align="center"> <strong>ID</strong> </div> </td> <td> <div align="center"> <strong>Name</strong> </div> </td> <td> <div align="center"> <strong>Address</strong> </div> </td> <td> <div align="center"> <strong>City</strong> </div> </td> <td> <div align="center"> <strong>State</strong> </div> </td> <td> <div align="center"> <strong>Postal Code</strong> </div> </td> </tr> </tbody> <tbody > <xsl:for-each select="tblFaci1ities"> <!-- Cache the current node incase the a field is formatted --> <xsl:value-of select="fx:CacheCurrentNode(.)"> <tr> <td> <xsl :value-of select="FacilityID"/> </td> <td> <xsl:value-of select="FacilityName"> </td> <td> <xsl :value-of select="FacilityAddress"/> </td> <td> <xsl :value-of select="FacilityCity"> </td> <td> <xsl :value-of select="FacilityStateOrProvince"/> </td> <td> <xsl:value-of select="FacilityPostalCode"> </td> </tr> </xsl:for-each> </tbody> </table> </body> </html> </xsl:template> <msxsl :script language="VBScript" implements-prefix="fx" xmlns:msxsl="urn:schemas-mi crosoft-com:xslt"> <![CDATA[ ... Standard data conversion VBScript functions included by Access removed for brevity. ... ]]></msxsl:script> </xsl:stylesheet>
Notice that the file begins with a comment and the required root element tag. The tag following the root tag specifies that the output format is HTML-ideal for a Web page. The next tag (<xsl:template ...>) identifies the start of the template.
What follows is pure HTML-the tags you would expect to see in an HTML page to define a table layout and its headings. About two-thirds of the way into the listing, following the tags that define the column headings (ID, Name, Address, City, State, and Postal Code), you can find an <xsl:for-each ...> tag that identifies the XML table that provides the data. This directive is followed by six blocks of <xsl:value-of ...> directives, one for each field in the table.
Following the end of the template (</xsl:template> tag), Access includes a large amount of code written in VBScript that defines a series of data transformation functions and the VBScript equivalent of many Access built-in functions. The style sheet doesn’t need most of these functions for a simple table like tblFacilities, but it might need them if you have exported the result of a query to XML and that query uses functions in expressions. As Access developers, we become spoiled by the broad range of Visual Basic functions that we can use in queries, but many simply don’t exist in VBScript. So, Access must include script that emulates these functions.
The script ends, as you would expect, with an </xsl:stylesheet> end tag to terminate the stylesheet object.
The Presentation Layout Extension Package (.xsx)
Access 2007 also creates a presentation layout extension file that includes a few simple , \ \ directives that are used by any designer package (such as Microsoft Expression Web). Your designer might modify this file if you choose to edit the XML file. Because we created the sample files on a Western language system, the sample file contains a single directive to the designer to lay out text from left to right.
The Web Package (.htm)
The final file that you can optionally create when you export to XML is an HTML (.htm) file containing the commands necessary to bring together the other four files and output standard HTML that your browser can display. This file contains VBScript that executes in the ONLOAD event of the Web page, and the script uses the Document Object Model (DOM) to convert the XML information to HTML descriptive tags. The Web package file (tblFacilities.htm) for the tblFacilities table is as follows:
<HTML xmlns:signature="urn:schemas-mi crosoft-com:office:access"> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=UTF-8"/> </HEAD> <BODY ONLOAD="ApplyTransform()"> </BODY> <SCRIPT LANGUAGE="VBScript"> Option Explicit Function ApplyTransform() Dim objData, objStyle Set objData=CreateDOM LoadDOM objData, "tblFacilities.xml" Set objStyle=CreateDOM LoadDOM objStyle, "tblFacilities.xsl" Document.Open "text/html","replace" Document. Wri te objData.TransformNode(objStyle) End Function Function CreateDOM() On Error Resume Next Dim tmpDOM Set tmpDOM=Nothing Set tmpDOM=CreateObject("MSXML2.DOMDocument.S.O") If tmpDOM Is Nothing Then Set tmpDOM=CreateObject("MSXML2.DOMDocument.4.0") End If If tmpDOM Is Nothing Then Set tmpDOM=CreateObject("MSXML.DOMDocument") End If Set CreateDOM=tmpDOM End Function Function LoadDOM(objDOM, strXMLFile) objDOM.Async=False objDOM.Load strXMLFile If (objDOM.ParseError.ErrorCode <> 0) Then MsgBox objDOM.ParseError.Reason End If End Function </SCRIPT> </HTML> Notice that the script code doesn’t use the XSD file at all! It doesn’t need this file because the data file (.xml) and the presentation file (.xsl) contain all the information necessary to create the Web page. The Document.Write statement is the command that actually writes the final HTML to your browser to display the table. If you open the tblFacilities.htm file in your browser, you’ll see the result shown in Figure 23–1.
Figure 23–1: The tblFacilities.htm file displayed in Windows Internet Explorer shows the data and the fields in the tblFacilities table.
| Note | Depending upon your browser settings, you might not be able to see the page because you have your browser security options set to disable running scripts. If you’re using Internet Explorer version 6, or 7, click the top of the browser window (where the warning message is displayed), click Allow Blocked Content, and then click Yes in the Security Warning dialog box to view the page. |
If you’re using Internet Explorer 7, you can click the Page button and then click View Source to open in Notepad the final HTML generated by the script. The final HTML the browser uses looks like the HTML you found in the presentation file (.xsl) with the data merged from the data file (.xml).
For more information on the HTML and XML standards, you can visit the Web site of the World Wide Web Consortium (W3C) at www.w3.org/.
EAN: 2147483647
Pages: 234