Listing 2 shows a mapping schema that maps publications and authors from the XML document into the Titles and Authors tables, respectively. The mapping schema is an XML Schema Definition (XSD) schema augmented with annotations. In Listing 2, the annotations appear in red. I won't explain the purpose of each annotation here, but let's examine the sql:relationship annotation at callout A in Listing 2 because that annotation specifies the foreign key relationship between the tables.
The sql:relationship annotation in the code at callout A uses the parent, parent-key, child, and child-key attributes to specify the primary key (AuthId) and foreign key (AuthRef) columns in the Authors and Titles tables. The innovation in SQLXML 3.0 SP2 is that XML Bulk Load detects the identity columns, buffers the value from the identity column (AuthId), then stores the value in the foreign key column (AuthRef). But for this particular example, you first need to do one more thing: reverse the order in which XML Bulk Load creates rows in the Titles and Authors tables.
XML Bulk Load first creates the row in the table specified by the parent attribute, then creates the row in the table specified by the child attribute. In this example, XML Bulk Load would create the row in the Titles table before creating the row in the Authors table. But creating the rows in this order is problematic because the row in the Titles table must store the value from the Authors table's identity column, which is generated when the row is inserted into the Authors table. To solve this problem, you need to add the inverse attribute with the value true to the sql:relationship annotation, as callout A shows. The inverse attribute tells XML Bulk Load to reverse the order it creates the rows in. After you add the inverse attribute, the mapping schema is complete.
Pulling the Pieces Together
The remaining task is to create a program that uses the XML Bulk Load COM to reference the mapping schema and load the data into the database. The VBScript code that Listing 3 shows is an example of one such program. The script creates the XML Bulk Load COM object, sets the connection string, specifies the file location for an error log, and sets the KeepIdentity property to false. KeepIdentity influences how XML Bulk Load stores identity values. If KeepIdentity is true (the default), XML Bulk Load stores the values specified in the XML document. If KeepIdentity is false, SQL Server generates the values when new rows are inserted. Although Figure 1's document doesn't include identity values, you need to set the value to false so that SQL Server will generate the value for the AuthId column in the Authors table. The final step in the script is to call the Execute method, which takes the paths to the mapping schema and data file as parameters. XML Bulk Load takes it from here and loads the data into your database.
To use the code for this example, first create a SQL Server database called Nov2003 and run the T-SQL code from Listing 1 to create the Titles and Authors tables. Then, save the data file from Figure 1 to a file called publications.xml, save the mapping schema from Listing 2 to a file called pubs.xsd, and save the VBScript code from Listing 3 to a file called XBL.vbs on your computer. You need to have Visual Basic Scripting Edition installed on your machine to execute the script. Before running the script, you need to change the connection string to match your local environment. Then, bring up a command prompt and run the VBScript code to load the data into the tables. You can verify the successful operation of XML Bulk Load by using Query Analyzer to query the rows from the Titles and Authors tables. Note that the values in the Titles table's AuthRef column contain the correct references to rows in the Authors table.
Loading very large XML documents into a normalized database that contains identity columns used to require you to write a potentially complicated program. As this example demonstrates, Microsoft has enhanced the value of XML Bulk Load by adding the ability to automatically store values from identity columns in foreign keys.