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

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

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

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

Ken Getz; Paul Litwin; Andy Baron

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

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

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










Recipe 18.2 Import XML into Existing Tables



18.2.1 Problem


When you import XML into a new
table, the data is read as Text, regardless of whether some elements
contain numeric values. You need the XML data to conform to certain
data types for each element.


18.2.2 Solution


The simplest way to solve the problem is to create a table structure
prior to importing the data. In Recipe 18.1,
all of the columns in the new table are created as Text with a
maximum size of 255, as shown in Figure 18-4 where
the Price column is selected.


Figure 18-4. The numeric data for Price is imported as Text


Follow these steps to create a
table structure that
better matches your XML data:

  1. Open the

    18-02.MDB database.

  2. Create a new table named Car (to match the name of the first element
    that follows the root element in the XML file). It's
    important that the name of the table match this element.

  3. Create the columns and data type shown in Table 18-1.


Table 18-1. Columns and data types for Car

Column name


Data type


Make


Text 20


Model


Text 20


Price


Currency

  1. Save the table and close the Table Designer.

  2. Choose File Get External Data Import to display
    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 the XML file you want to
    import, and click Import, which will load the XML Import dialog box.
    Select the 18-02.xml file and click Import.

  5. Click Options and choose Append Data to Existing Table(s) as shown in
    Figure 18-5. Click OK and then OK again.



Figure 18-5. Appending XML data to an existing table


  1. Open the Car table in datasheet view. You will see that the XML data
    has been appended to the table correctly.



18.2.3 Discussion


This example works because there is an exact mapping between the
element names in the XML file and the table and field names in the
Access Car table, so Access can figure out where the data is supposed
to go. As long as the data in the XML file does not have any
anomalies, then this solution will work nicely.

Access will be unable to import the data in certain rows if there is
a data type mismatch. Consider the following XML file, 18-02-bad.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">
<Car>
<Make>Mini Cooper</Make>
<Model>S</Model>
<Price>20,000</Price>
</Car>
<Car>
<Make>Ford</Make>
<Model>Edsel</Model>

<Price>unknown</Price>
</Car>
</dataroot>


The Price element for the second car, the Edsel, is unknown. The
Price column in the Car table is expecting a currency value. When you
perform the insert, appending to the existing table,
you'll see the error message shown in Figure 18-6.


Figure 18-6. Error message appending bad XML to an existing table


If you open the ImportErrors table, you'll see the
information shown in Figure 18-7.


Figure 18-7. The ImportErrors table shows error information for data that failed to be appended to the table


If you open the Car table as shown in Figure 18-8,
you'll see that the Make and Model for the Edsel row
of data imported correctly. However, the Price for that row is set to
0, the default value.


Figure 18-8. Access fails to import data from an XML file where it can't convert the data to the correct data type


If there is no default value specified for the Price column, then no
value will be entered for Price, but Make and Model will be imported
successfully. If the Required property for Price is set to Yes, then
the entire row will be skipped, and you'll have an
additional row in the ImportErrors table with the following data in
the Error Message column:


Microsoft JET Database Engine: The field
`Car.Price' cannot contain a Null
value because the Required property for this field is set to True.
Enter a value in this field.



/ 232