Importing XML Data to Tables

Most of this chapter is devoted to exporting XML documents from Access 2003 and SQL Server 2000. Access 2003 also can import well-formed, element-centric XML data documents to new tables or append data to existing tables. Access's 2002's XML import feature was limited to XML files having elements from a single table or query. If you wanted to import order and line item data, for example, you needed an XML data document for each table. To maintain referential integrity, you had to append the orders document and then the line items document in separate operations.

graphics/new.gif

Access 2003's new import XML dialog lets you import data to multiple tables from a single XML document and its XML schema. If you don't have an .xsd file for the document, Access 2003 infers the structure of the document and can create a schema for it. Another new Access 2003 XML feature is the capability to create empty related tables from an XML schema (.xsd) document. You also can transform the XML data during import with custom .xsl files.

The ability to generate tables from XML schema and import XML data to Jet and SQL Server tables will become increasingly important as Microsoft and other software publishers, as well as many industries, move to XML as the preferred method of interchanging data. Most current standards for XML business documents provide XML schemas to validate instances of the documents.

Importing a Flat XML Data Document

graphics/power_tools.gif

This simple example uses the Top100SQL.xml document file you created in the earlier "Returning XML Documents from HTTP Queries" section from the Sales Totals by Amount view. If you didn't save the Top100SQL.xml file, there's a copy in the \Seua11\Chaptr23 folder of the accompanying CD-ROM.

To import the Top100SQL.xml document to a new table and append another set of records to the table, do this:

  1. Open a database or project that doesn't have the Sales Totals by Amount query or view. The XMLXSL23.mdb sample database or, if you created it, the XMLXSL23.adp project is a good candidate. ADP are used in this and the following examples.

  2. Choose File, Get External Data, Import to open the Import dialog, select XML (*.xml, *.xsd) in the Files of Type list, and navigate to the location where you saved the Top100SQL.xml file.

  3. Double-click the Top100SQL.xml file to close the Import dialog and open the new Import XML dialog, which displays the name of the source query as a Tables subnode. Expand the Sales Totals by Amount node to display the view columns.

  4. Click the Options button to expand the dialog and display the three Import Options shown in Figure 23.26. For an initial test, accept the default Structure and Data option.

    Figure 23.26. The new Import XML dialog displays the name of the source table or view from which you created the data document and its fields or columns.

    graphics/23fig26.gif

  5. Click OK to create the table with the imported values. Click OK to acknowledge the "Finished importing..." message, and press F5 to refresh the Database window if the new table isn't present.

  6. graphics/datasheet_view.gif Open the new Sales Totals by Amount table in Datasheet view, which displays SQL Server's default XML text formats for all fields, because you don't have a schema for the XML file (see Figure 23.27).

    Figure 23.27. The imported table consists of four text fields having the default text format. Datasheet view of the table is the same in Jet and ADP.

    graphics/23fig27.jpg

  7. graphics/design_view.gif Change to Table Design view.

  8. If you're using a Jet database, change the data type of the SaleAmount field to Currency and the OrderID field to Number, Long Integer. A Jet database won't accept a change of the ShippedDate field, because Jet can't handle the ISO 8601 YYYY-MM-DDThh:mm:ss format designed by the XML Schema Part 2: Datatypes specification for the dateTime data type. You must have a schema that specifies the dateTime data type for the DateTime field to import dates successfully.

    For an SQL Server database, change the data type of the SaleAmount field to money, OrderID to int, and ShippedDate to datetime (see Figure 23.28). Not surprisingly, SQL Server can handle ISO 8601. Optionally, change the Length property for CompanyName to 40.

    Figure 23.28. Change SQL Server field data types to those appropriate for the type of data in each column. You also can rearrange the sequence of the fields, if you want.

    graphics/23fig28.jpg

  9. graphics/datasheet_view.gif Return to Datasheet view, save your changes, and acknowledge the warning message about field data sizes. For an SQL Server table, the Validation Warnings message opens (see Figure 23.29). Figure 23.30 shows the modified SQL Server table in Datasheet view. Click Yes to acknowledge that data might be lost by changes to field data sizes.

    Figure 23.29. This SQL Server warning message advises that data might be lost as a result of shortening the field length from nvarchar(255) to int or datetime, but not money. The Save Text File button saves a text file of the warnings, not the data.

    graphics/23fig29.gif

    Figure 23.30. SQL Server successfully changes the data types of three fields; Jet can't change the ShippedDate data type from Text to DateTime.

    graphics/23fig30.jpg

    Caution

    Don't disregard a warning message that states that rows will be lost. This message occurs when Jet or SQL Server can't handle the data type change. Cancel the operation and return the data type for the field to Text or nvarchar.

  10. Test the ability of your redesigned table to accept updates from XML data documents having the same structure. Close the table, and repeat steps 2 5, except select the Append Data to Existing Table(s) option in step 4.

  11. graphics/datasheet_view.gif Open the table in Datasheet view, verify that the table now has 200 rows, and the appended records (101 and greater) appear the same as the first 100. You can disregard "Not all your data was imported..." error messages, if they occur, after verifying the new row count value.

Note

SQL Server tables imported from XML documents are read only in datasheet view, because they lack a unique record identifier (primary key). You can edit any Jet table imported from an XML document.


Importing hundreds of thousands of records as XML to populate tables isn't likely to replace SQL Server's bulk copy process (BCP), which uses conventional tab- or comma-delimited, or fixed-width text files. BCP data import is much faster than XML import, and conventional text files are much smaller than XML data files for the same number of records. XML import is better suited to making incremental additions to tables. Access 2003 XML import feature makes adding records to SQL Server tables considerably easier than using BCP or SQL Server's Data Transformation Service (DTS).

Note

graphics/globe.gif

SQLXML, an add-on to SQL Server mentioned earlier in the chapter, offers a high-speed XML bulk load feature and supports XML updategrams for INSERT, UPDATE, and DELETE operations with XML documents. To download SQLXML, which adds a SQLXML choice to your Programs menu, go to http://www.microsoft.com/sql/downloads/default.htm. You must have the SQL Server Client Tools installed to run SQLXML's setup program. SQLXML 3.0 SP 1 was current when this book was written.


Importing Data with an XML Schema

Access can import XML schema in XSD format to create an empty table to which you append XML data that conforms to the schema. If the schema contains od:jetType="datatype" and od:sqlSType="datatype" attributes, the schema specifies the Jet or SQL Server field data types when you create the empty table.

To export and then import the schema and then add data for the Sales Totals by Amount table to an Jet or SQL Server database, do this:

  1. graphics/window_database.gif With the Sales Totals by Amount table selected in the Database window, choose File, Export, select XML (*.xml) in the Files of Type list, change the file name to TopOrdersSQL, and click Export.

  2. In the Export XML dialog, clear the Data (XML) and mark the Schema of the Data (XSD) options, and click OK to export TopOrdersSQL.xsd.

  3. Choose File, Get External Data, Import, and double-click the TopOrdersSQL.xsd item to open the XML Import dialog.

  4. Click OK to import the schema and data, and acknowledge the "Finished importing..." message.

  5. graphics/design_view.gif Open the empty table in Design view to verify that the data types are the same as those you specified in the preceding section.

  6. graphics/datasheet_view.gif Return to Datasheet view, and append the 100 records from the Top100SQL.xml data document to confirm the validity of the imported schema.

Sending XML schema and data documents over the Internet to create and populate Jet and, especially, SQL Server tables is simpler than other alternatives. Many firewalls now reject email enclosures in non-text formats such as Jet .mdb and SQL Server .mdf files. XML documents are text files, so they pass through firewalls with no problems.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net