Recipe 18.3 Import XML Using a Schema (XSD)

18.3.1 Problem

You need to import an XML file that has a certain schema. but don't know ahead of time what the schema will be. You need to create a table that has the correct data types, and then generate a new AutoNumber primary key for each row appended to the table.

18.3.2 Solution

If you want to apply a particular schema when you import an XML file, you need to import the schema file, or XSD, before importing the data. If you have already created a table with the desired structure, you can have Access save the schema for you by exporting the table and saving the schema as a separate file. This is an easy way to use Access to create schema files. You can also manually create a schema file by using a text editor, and save it with an XSD file extension. You also can use a schema file that has been provided to you by your company or by a partner. Follow these steps to import a schema file and then an XML file:

Figure 18-9. Importing an XSD file

18.3.3 Discussion

Once you have a schema file, you can view its structure using Internet Explorer, which indents all of the schema information for you, as shown in Figure 18-10.

Figure 18-10. The XSD file used to create the Car table

Visual Studio .NET provides an excellent tool for viewing and modifying XSD schema files. When you open a schema file in Visual Studio .NET, you get a graphical designer very similar to the Access Relationships window.

The file references two schemas. The xsd namespace references the XML Schema standard at the W3C's web site. The od namespace references the Office data schema developed by Microsoft for Office data types:

- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
   xmlns:od="urn:schemas-microsoft-com:officedata">

The dataroot element is defined using a complexType XML Schema element, which enables it to contain other elements—in this case, Car elements. The maxOccurs="unbounded" attribute value means that the contents of the dataroot element, in this case Car, can occur an unlimited number of times. The xsd:element ref attribute indicates that Car is defined elsewhere in this XSD file:

<xsd:element name="dataroot">
  <xsd:complexType>
   <xsd:sequence>
    <xsd:element ref="Car" minOccurs="0" maxOccurs="unbounded" /> 
   </xsd:sequence>
   <xsd:attribute name="generated" type="xsd:dateTime" /> 
  </xsd:complexType>
</xsd:element>

The Car element is defined next, which comprises the table definition. Application-specific information is stored in the <xsd:annotation> and <xsd:appinfo> tags, which Access uses to describe indexes defined on the table. This allows Access to define characteristics that aren't part of the W3C schema definition vocabulary. These Access-specific items defined by the Office data schema are referenced by the od namespace. The <xsd:complexType> tag means that the Car data type itself is a complex type that contains other types:

<xsd:element name="Car">
<xsd:annotation>
  <xsd:appinfo>
    <od:index index-name="PrimaryKey" index-key="ID" primary="yes" 
     unique="yes" clustered="no" /> 
    <od:index index-name="ID" index-key="ID" primary="no" 
     unique="no" clustered="no" /> 
  </xsd:appinfo>
</xsd:annotation>
<xsd:complexType>

The next section of the XSD file defines the columns of the table, their data types, sizes, and properties. Note that the ID element is tagged with both the od:jetType="autonumber" and the od:sqlSType="int" attributes:

  <xsd:sequence>
    <xsd:element name="ID" minOccurs="1" od:jetType="autonumber" 
    od:sqlSType="int" od:autoUnique="yes" od:nonNullable="yes" type="xsd:int" /> 
   <xsd:element name="Make" minOccurs="0" od:jetType="text" 
    od:sqlSType="nvarchar">
    <xsd:simpleType>
      <xsd:restriction base="xsd:string">
      <xsd:maxLength value="20" /> 
    </xsd:restriction>
    </xsd:simpleType>
    </xsd:element>
      <xsd:element name="Model" minOccurs="0" od:jetType="text" 
       od:sqlSType="nvarchar">
      <xsd:simpleType>
      <xsd:restriction base="xsd:string">
      <xsd:maxLength value="20" /> 
    </xsd:restriction>
    </xsd:simpleType>
    </xsd:element>
    <xsd:element name="Price" minOccurs="1" od:jetType="currency" 
     od:sqlSType="money" od:nonNullable="yes" type="xsd:double" /> 
  </xsd:sequence>
  </xsd:complexType>
</xsd:element>

All of the columns are defined with both Jet and equivalent SQL Server data types. This allows you to import the XSD file into an Access Project (.adp). One step you would have to perform manually for SQL Server is setting the Identity property of the SQL Server table after you have imported the XSD file and prior to importing the XML file.

18.3.4 See Also

The World Wide Web Consortium (W3C) site contains the following primer on XML Schema:

http://www.w3.org/TR/xmlschema-0/

The following MSDN article gives a good overview of XML Schema:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnxml/html/understandxsd.asp?frame=true