Importing and Exporting XSD Data in Microsoft Office Access 2003
Frank Rice
Microsoft Corporation
February 2004
Applies to:
Microsoft® Office Access 2003
Summary: Microsoft Office Access 2003 introduces a feature that allows you to import an XSD document and create an XSD document on export. This article explains what an XML schema is, describes the XSD elements that Access supports, and describes the data type conversion during import and export. (10 printed pages)
Contents
Introduction
Why Use XML Schemas?
XSD Declarations and Definitions Supported by Access
XSD, Jet, and SQL Data Types Created when Importing or Exporting Access Data
Importing and Exporting XML and XSD Data in Access
Conclusion
Introduction
Microsoft® Office Access 2003 supports both the import of XML schema definition (XSD) files and the creation of XSD files on export. What are XSD files? Just as database schemas define the tables, columns, and data types for a database, Extensible Markup Language (XML) schemas help define the structure of an XML document by using a set of predefined elements that allow for the validation of that data by an XML parser. Specifically, schemas provide a model for an XML data document which defines the arrangement of tags and text within all documents referencing the schema.
An XML parser that supports the XSD recommendation, as defined by the World Wide Web Consortium (W3C), knows how to react to the predefined elements when validating the XML document. An XML document that conforms to the structure of the schema is said to be valid.
Note The W3C is a non-governmental standards body that makes recommendations for standards including for XML and XSD. Because the W3C is a non-governmental body, it can only make recommendations which, in this case, essentially carry the same weight as a standard.
Why Use XML Schemas?
To understand how schemas can help you, suppose that you write an application that accepts XML from another organization. To do this, you need to convey to the organization the required structure of the XML file so that they can provide you with XML data that can be parsed correctly. How would you do this? One method would be to e-mail the structure as: "The customer last name field should be a minimum of two characters and a maximum of 20 characters. The field is required." Needless to say, this would be awkward and time consuming for any more than a moderate document. In addition, it would be inefficient if you need to update the XML format. Instead of risking breaking your application by malformed XML or having to rewrite the same implementation for another document, you can explicitly convey the structure in an XML schema. This also allows you to provide the structure to other departments and organizations so that they know how to format their XML document to effectively share data. In addition, they can provide their schema to you.
XSD Declarations and Definitions Supported by Access
As stated earlier, the latest version of Access supports importing XSD and creating XSD documents on export. The XSD language, which is supported by the World Wide Web Consortium (W3C), contains dozens of definitions and declaration commands which support describing the structure of documents. However, Access supports only a subset of these.
Note The schema namespace used in Access 2003 is different than that used in Access 2002. The release namespace used in Access 2003 is http://www.w3.org/2001/XMLSchema.
Table 1. Supported definitions and declarations
| Definitions/declarations | Example | Purpose |
|---|---|---|
| xsd:complexType | <xsd:complexType name="Name"> <xsd:sequence> <xsd:element name="firstname" type="xsd:string"/> <xsd:element name="lastname" type="xsd:string"/> </xsd:sequence> </xsd:complexType> |
Defines a type structure that typically contains a set of element declarations, element references, and attribute declarations. |
| xsd:simpleType | <xsd:simpleType name="SKU"> <xsd:restriction base="xsd:string"> <xsd:pattern value="\d(3)-[A-Z](2)"/> </xsd:restriction> </xsd:simpleType> |
Creates a data type from a built-in XML schema data type. |
| xsd:element | <xsd:element name="street" type="xsd:string" /> | Declares an instance of an element. |
| xsd:schema | <xsd:schema xmlns:xsd="http://www.w3c.org" /> | Declares the Document element of the schema document. Contains all other elements. |
| xsd:restriction | <xsd:simpleType name="SKU"> <xsd:restriction base="xsd:string"> <xsd:pattern value="\d(3)-[A-Z](2)"/> </xsd:restriction> </xsd:simpleType> |
When creating a custom type, indicates what built-in XSL data type this type is based on and identifies the range of values that it can contain. |
| xsd:appinfo | <xsd:appinfo> <xsd:hasProperty name="ordered" value="false"/> <xsd:hasProperty name="numeric" value="true"/> </xsd:appinfo> |
Provides additional information for tools, style sheets, or other applications. |
| xsd:annotation | <xsd:annotation> <xsd:appinfo> <xsd:hasProperty name="ordered" value="false"/> <xsd:hasProperty name="numeric" value="true"/> </xsd:appinfo> </xsd:annotation> |
Contains elements that describe the schema for both people and applications. |
| xsd:choice | <xsd:choice> <xsd:element name="shipAndBillAddress"/> <xsd:element name="singleUSAddress"/> </xsd:choice> |
Grouping element that allows only one child node to appear in a document. |
| xsd:all | <xsd:all> <xsd:element name="shipTo" type="USAddress"/> <xsd:element name="billTo" type="USAddress"/> <xsd:element name="items" type="Items"/> </xsd:all> |
Provides grouping element that requires all elements to appear once or not at all. |
| od:jetType= | <xsd:element name="EmployeeID" od:jettype="autonumber"/> | Defines the Jet data type for an element. |
| od:sqlSType= | <xsd:element name="EmployeeID" od:sqlSType="int"/> | Defines the Microsoft SQL Server data type for an element. |
| od:autounique= | <xsd:element name="EmployeeID" od:autounique="yes"/> | Defines a Boolean data type representing an autoincremented identity column. |
| od:nonNullable= | <xsd:element name="EmployeeID" od:nonNullable="yes"/> | Defines a Boolean data type indicating whether a column can contain a null value or not. |
XSD, Jet, and SQL Data Types Created when Importing or Exporting Access Data
In addition to supporting specific XSD declarations and definitions, Access also converts XSD data types to native data types when importing or exporting data to XSD document that are consistent with those data types used by Access. The following table shows the equivalent XSD and SQL types created when exporting Jet data types from Access.
Table 2. XSD and SQL data types created when exporting Jet data types
| Jet data types (common name) | Jet data type | SQL data type | XSD data type |
|---|---|---|---|
| Text | text | nvarchar | string |
| Memo | memo | ntext | string |
| Number, byte | byte | tinyint | unsignedByte |
| Number, integer | long | smallint | short |
| Number, long integer | longinteger | int | int |
| Number, single | single | real | float |
| Number, double | double | float | double |
| Number, repln ID | replicationid | uniqueidentifier | string |
| Number, decimal | decimal | decimal | decimal |
| Date Time | datetime | datetime | dateTime |
| Currency | currency | money | double |
| Autonumber | autonumber replicationid |
int uniqueidentifier |
int |
| Yes/No | yesno | bit | boolean |
| OLE Object | oleobject | image | base64Binary |
| Hyperlink | hyperlink | ntext | string |
| Date (Legacy) | date | datetime | date |
| Time (Legacy) | time | datetime | time |
| Binary | binary | varbinary image |
base64Binary |
The following table shows equivalent Jet and XSD data types created when exporting SQL data types.
Table 3. Jet and XSD data types created when exporting SQL data types
| SQL data type | Jet data type | XSD data type |
|---|---|---|
| bigint | longinteger | long |
| binary | binary oleobject |
base64Binary |
| bit | yesno | boolean |
| char | text memo |
string |
| datetime | datetime | dateTime |
| decimal | decimal text |
decimal |
| float | double | double |
| image | oleobject | base64Binary |
| int | longinteger | int |
| money | currency | double |
| nchar | text memo |
String |
| ntext | memo | string |
| numeric | decimal text |
decimal |
| nvarchar | text memo |
string |
| real | single | float |
| smalldatetime | datetime | dateTime |
| smallint | integer | short |
| smallmoney | currency | double |
| sqlvariant* | binary | string |
| text | memo | string |
| timestamp | binary | base64Binary |
| tinyint | byte | unsignedbyte |
| uniqueidentifier | replicationid | string |
| varbinary | binary oleobject |
base64Binary |
| varchar | text memo |
string |
*Only MDAC 2.6 and later support this data type. When exporting from previous versions, a decimal type is written out.
Table 4. Jet and SQL data types created when you import XSD data types
| XSD data type | Jet data type | SQL data type |
|---|---|---|
| anyType | memo | ntext |
| base64Binary hexBinary |
oleobject | varbinary |
| boolean | yesno | bit |
| byte | number, integer | smallint |
| normalizedString | memo | ntext |
| date | datetime | dateTime |
| decimal | number, decimal | decimal |
| single | text | bigint |
| Double | number, double | real |
| ENTITIES | memo | ntext |
| ENTITY | memo | ntext |
| float | number, single | float |
| ID | text | nvarchar |
| IDREF | text | nvarchar |
| IDREFS | text | nvarchar |
| Int | number, long integer | int |
| integer | number, decimal | numeric |
| language | memo | ntext |
| long* | number, text | bigint |
| gMonth | Text | nvarchar |
| Name | Memo | ntext |
| NCName | memo | ntext |
| negativeInteger | number, decimal | numeric |
| NMTOKEN | memo | ntext |
| NMTOKENS | memo | ntext |
| nonNegativeInteger | number, decimal | numeric |
| nonPositiveInteger | number, decimal | numeric |
| NOTATION | text | nvarchar |
| positiveInteger | number, decimal | numeric |
| Qname | memo | ntext |
| gMonthDay | text | nvarchar |
| gDay | text | nvarchar |
| short | number, integer | smallint |
| string (length <=255) | text | nvarchar |
| string (length >255) | memo | ntext |
| time | text | nvarchar |
| duration | text | nvarchar |
| dateTime | datetime | datetime |
| unsignedByte | number, byte | tinyint |
| unsignedInt | number, decimal | numeric |
| unsignedLong** | number, text | nvarchar*** |
| unsignedShort | number, integer | int |
| anyURI | Memo | ntext |
| gYear | number, integer | smallint |
| gYearMonth | Text | nvarchar |
| token | memo | ntext |
| language | text | nvarchar |
*The reason this field is converted to text by Access is that per the W3C, the range for the xsd:long type is -9,233,372,036,854,775,808 to +9,233,372,036,854,775,807 which is larger than any corresponding numeric Jet type.
**The maximum range for this xsd:long type is -18446744073709551615 to +18446744073709551615.
***The maximum range for this nvarchar type is -18446744073709551615 to +18446744073709551615.
Note If Access cannot determine the type or encounters a type it not recognized, the field will be created as Text(255).
Importing and Exporting XML and XSD Data in Access
In Access, you can import data from an XML document or an XSD schema. The following steps describe how to do this:
Open a database, or switch to the Database window for the open database.
On the File menu, point to Get External Data, and then click Import.
In the Import dialog box, in the Files Of Type box, select XML Documents (*.xml;*.xsd).
Note importing XML into an Access project (ADP) requires that the MSDE or Microsoft SQL Server™ installed locally.
Click the arrow to the right of the Look In box, select the drive and folder where the file is located, and then double-click its icon.
The Import XML dialog box displays a list of tables contained within the XML document or defined in the XSD schema.
Note All of the tables shown in the list are imported. You cannot select a subset of tables to import.
To start importing the file, click OK. This completes the import procedure.
Note For most records that cause an error, Access creates and adds a row to a table called Import Errors. To view the list of errors that were encountered, open the Import Errors table from the Database window.
To set options for importing XML Documents, select one of the following under Import Options:
To import just the structure of the table, click Structure Only.
Note If you are importing an XSD file (contains no data), the Structure Only option is the only one available and is automatically selected
To import the structure of the table and the data, click Structure and Data.
To append the data to an existing table, click Append Data To Existing Table(s).
Note The import options are available only when importing an XML document. They are unavailable when importing an XSD schema file.
Click OK to start importing, or proceed to the next step.
Note For the first two options above, if a table already exists with the same name, a new table is created with a number appended to the name. Otherwise, a new table is created.
You can also output data to XML by exporting to an XML document. You can also export a schema to specify the structure of the XML document as well as a style sheet to describe the presentation of the data.
- In the Database window, click the name of the table, query, form, or report that you want to export, and then on the File menu, click Export.
- In the Save as type box, click XML (*.xml).
- Click the arrow to the right of the Save in box, and select the drive or folder to export to.
- In the File name box, type a name for the file, and then click Export.
- In the Export XML dialog box, do one or more of the following:
- To export the data to an XML document, select Data (XML).
- To export the schema to an XML file, select Schema of the data.
Conclusion
This article looked at how Access imports and exports XML and XSD data. It also described the data type conversions that occur during these operations. Using XSD documents aid in ensuring that your XML documents are valid documents and that you can successfully interchange data with other departments and organizations.