Export (0) Print
Expand All

Importing and Exporting XSD Data in Microsoft Office Access 2003

Office 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)


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


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:element name="firstname" type="xsd:string"/>
<xsd:element name="lastname" type="xsd:string"/>
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)"/>
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)"/>
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"/>
Provides additional information for tools, style sheets, or other applications.
xsd:annotation <xsd:annotation>
<xsd:hasProperty name="ordered" value="false"/>
<xsd:hasProperty name="numeric" value="true"/>
Contains elements that describe the schema for both people and applications.
xsd:choice <xsd:choice>
<xsd:element name="shipAndBillAddress"/>
<xsd:element name="singleUSAddress"/>
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"/>
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
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

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
bit yesno boolean
char text
datetime datetime dateTime
decimal decimal
float double double
image oleobject base64Binary
int longinteger int
money currency double
nchar text
ntext memo string
numeric decimal
nvarchar text
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
varchar text

*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
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:

  1. Open a database, or switch to the Database window for the open database.
  2. On the File menu, point to Get External Data, and then click Import.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.

  1. 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.
  2. In the Save as type box, click XML (*.xml).
  3. Click the arrow to the right of the Save in box, and select the drive or folder to export to.
  4. In the File name box, type a name for the file, and then click Export.
  5. 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.


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.

© 2015 Microsoft