Page 2 - Exporting XML from MS Access 2003

(Page 2 of 4 )

The Export drop-down menu item can be accessed from the File menu item. Highlighting the Argentina table and clicking on the Export drop-down pops up the window shown in the next picture. By default, it opens up the My Documents folder to save the export to one of several MS Access database file extensions. Since we would like to host it on the intranet, the location shown in the next picture is chosen. Since the XML format is the preferred format, this choice is also made as shown. Additionally a name for the file is needed, herein AccXml.xml was used.

Clicking on the Export button now brings up the window Export XML shown in the next picture. Well, there is more to exporting than simply producing a single file. The default export takes in the Data (XML) as well as Schema of the Data (XSD). Additionally the Presentation of your data (XSL), an extensible style language resulting in the XSL file, may also be chosen. This is not all; there is more as indicated by the More Options... button. We will take that up in another tutorial.

For now, accept the default and click OK. This does not give any message as to what happened, but sure enough, you will find the two files AccXml.xml and AccXml.xsd as shown in the next picture in the root directory of the IIS default web site.

The content of the AccXml.xml file is shown in the next paragraph, after removing all but one of the 'customers' to make the screen display shorter.

(Generated using XML and XSD combination)

<?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="AccXml.xsd" 
generated="2006-06-29T09:26:20">
<Argentina>
<CustomerID>OCEAN</CustomerID>
<OrderDate>1997-01-09T00:00:00</OrderDate>
<RequiredDate>1997-02-06T00:00:00</RequiredDate>
<ShipName>Océano Atlántico Ltda.</ShipName>
</Argentina>
<!-- More customers here-->
</dataroot>

It is a well formed document with reference to the standards of the W3C as well as to Microsoft's Office data structures (shown highlighted). The schema instance has two attributes: where the schema may be found, and the schema generated date and time. The data content is now enclosed in a root <dataroot..../>(in blue). This also happens to be the MS Access preferred format of an XML file that can be successfully imported. The AccXml.xsd which is also in XML format is shown in the next paragraph.

<?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="Argentina" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
<xsd:attribute name="generated" type="xsd:dateTime"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="Argentina">
<xsd:annotation>
<xsd:appinfo/>
</xsd:annotation>
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" minOccurs="0" 
od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="5"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="OrderDate" minOccurs="0" od:jetType="datetime" 
od:sqlSType="datetime" type="xsd:dateTime"/>
<xsd:element name="RequiredDate" minOccurs="0" od:jetType="datetime" 
od:sqlSType="datetime" type="xsd:dateTime"/>
<xsd:element name="ShipName" minOccurs="0" 
od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="40"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

This schema will not be described in detail as it is somewhat outside the scope of this tutorial, but suffice it to say that the generated XML file fully conforms with this schema. It details the data root, the nodes and the child nodes, as well as the data type and data size, as you may infer with reference to the original table. It also refers to the "Office data," the link that binds all Office products. Now that the XML file is available on the intranet, it will be interesting to see how the display will come out in the browser. The next picture shows a truncated portion.

Next: Exporting the extensible style language file, AccXml.xsl >>

More Microsoft Access Articles
More By Jayaram Krishnaswamy