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

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

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

Ken Getz; Paul Litwin; Andy Baron

نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
افزودن یادداشت
افزودن یادداشت جدید










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


  1. 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.

  2. To import the XML data, 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.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.

  5. 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


/ 232