Can I import data from XML files into SQL Server 2000?
Microsoft provides three ways to update SQL Server 2000 by using data from XML files: OpenXML, Updategrams, and XML Bulk Load. OpenXML ships with SQL Server 2000. Updategrams and XML Bulk Load became available in mid-February 2001 when Microsoft shipped XML for SQL Server 2000 Web Release 1. Web Release 1 represents the first supported, production-quality update to the original SQL Server 2000 XML functionality; you can download Web Release 1 from the Microsoft Developer Network (MSDN) Web site at http://msdn.microsoft.com/code/sample.asp?url=/msdn-files/027/001/554/msdncompositedoc.xml. To load small amounts of XML data into SQL Server—as much as 1MB (although 100KB is more typical)—OpenXML and Updategrams are good choices. SQL Server Magazine has already published several articles illustrating OpenXML and Updategram functionality (e.g., Michael Otey, "XML Updategrams," January 2001; Michael Otey, "Top XML-Integration Features," April 2001). Let's focus now on how XML Bulk Load works.
Microsoft designed XML Bulk Load, which is similar in functionality to the bulk copy program (bcp) and T-SQL BULK INSERT commands, to load large amounts of XML data into a database. But unlike bcp and BULK INSERT, which accept only tabular data representations (i.e., a set of rows with a fixed set of columns), XML Bulk Load supports loading XML hierarchies into one or more database tables. And unlike OpenXML and Updategrams, XML Bulk Load uses the streaming Microsoft XML Parser (MSXML) to process data rather than parsing the entire XML dataset into memory before processing. Using the streaming interface lets XML Bulk Load process datasets larger than 100MB without running out of memory. Let's look at an example of how to use XML Bulk Load to import data into SQL Server.
If you've used SQL Server 2000's XML View functionality, you've created an annotated schema that provides a mapping between the XML data in your dataset and a set of the database tables in your database. (For more information about XML View, see SQL Server Books Online—BOL.) You can then use the annotated schema in conjunction with an XPath query to retrieve data in XML format from your database. You can use that same annotated schema with XML Bulk Load to import data into SQL Server. (If you haven't used annotated schemas but have used bcp, an annotated schema is similar in function to a bcp format file.)
Constructing an annotated schema is the first step required to perform an XML Bulk Load. Listing 1 shows an annotated schema that loads the data in Web Listing 1 (see the More on the Web box for download information) into the database table in Listing 2. Because this example uses a simple schema, I used my trusty expert interface—Notepad—to create it. But Microsoft has provided a user-friendly tool called the XML View Mapper for constructing more complex annotated schemas. You can obtain the XML View Mapper and its documentation at http://msdn.microsoft.com/code/sample.asp?url=/msdn-files/027/001/443/msdncompositedoc.xml.
Let's examine the annotated schema that Listing 1 shows. The attributes with the sql prefix specify which elements and attributes map to which tables and columns in the database. The sql:relation attribute maps the Company element in the XML document to rows in the database's Companies table. The sql:field attributes map the attributes' values to columns in the Companies table. And, the sql:is-constant attribute specifies that the Company element is a schema root element and not mapped to the database. This schema provides all the information the XML Bulk Load facility needs to load the data into the database. The next step is to use the XML Bulk Load Object Model to execute the Bulk Load.
To illustrate the use of the XML Bulk Load Object Model, let's use a Visual Basic (VB) project. Listing 3 shows a code sample from the project that performs the Bulk Load. (The complete VB project is available for download; for instructions, see the More on the Web box.) The code sets the connection string to point to my database (remember, the annotated schema specifies the tables and columns), to use the SQL Server OLE DB provider, and to set the appropriate parameters. The code also sets up a path for an error log. If XML Bulk Load encounters an error while loading the data to the database, Bulk Load will write an error to the error log. Then, to execute the Bulk Load, the code passes to the Execute command in the SQLXMLBulkLoad object the names of the files that contain the annotated schema and the data. Voila! The data is now in the database.
Although this example is oversimplified, you can see that XML Bulk Load is simple and easy to use. XML Bulk Load also has some other useful features that handle situations you can encounter when you load large datasets into SQL Server. These features include the abilities to control transactional semantics, handle IDENTITY columns for relationships, handle NULLs, and lock semantics, among others. XML Bulk Load even has an option to create the database tables before loading the data, based on the annotated schema. Overall, XML Bulk Load is a handy and powerful tool for importing XML data into your database.
Editor's Note. Send your XML questions to Rich Rollman at
xmlquestions@sqlmag.com.
End of Article
Prev. page
1
[2]
next page -->
