Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. Open the

   18-03.MDB sample database.

2. Choose File Get External Data Import from the
   menu to load the Import dialog box.

3. In the Files of type drop-down list at the bottom of the dialog box,
   select XML (*.xml, *.xsd).

4. In the File name dialog box, navigate to 18-03.xsd, and click Import,
   which will load the XML Import dialog box shown in Figure 18-9. Note that the Options button is disabled.
   When you import a schema, there is no data involved. Click OK and
   then OK again.

Figure 18-9. Importing an XSD file

5. Open the Car table in design view. Note that the table includes a
   column named ID for a primary key as well as the columns for the data
   contained in the XML source file. Close the table.

6. To import the XML data, Choose File Get External Data
   Import from the menu to load the Import dialog box.

7. In the Files of type drop-down list at the bottom of the dialog box,
   select XML (*.xml, *.xsd).

8. In the File name dialog box, navigate to 18-03.xml, and click Import,
   which will load the XML Import dialog box. Expand the plus sign and
   note that the same three columns, Make, Model and Price are
   displayed. Click the Options button and select Append Data to
   Existing Table(s). Click OK and OK again.

9. Open the Car table in datasheet view. Note that an Autonumber value
   has been inserted for each row. Close the table.

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 elementsin 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