Using XML Bulk Load to Invoke a Stored Procedure

I'm trying to use XML Bulk Load to load XML into my database. However, instead of simply loading the data into database tables, I need to execute a stored procedure that runs business logic, then inserts the data into the database. How can I get XML Bulk Load to invoke a stored procedure without having to rewrite my existing stored procedures or duplicate their logic?

Unfortunately, XML Bulk Load can't invoke a stored procedure directly. XML Bulk Load is built to use either the SQL Bulk Insert facility or Visual Basic's (VB's) IRowsetFastLoad to load data into SQL Server. Neither method has the ability to invoke a stored procedure. An alternative solution is to create temporary tables for the data, then use XML Bulk Load to load the data into the temporary tables. Finally, you can write a new stored procedure to call your existing stored procedures by using the data from the temporary tables. XML Bulk Load Object Model (XBLOM) can help you with this task by creating a set of temporary tables. By using XBLOM, you can set the SchemaGen property to the value true to instruct XML Bulk Load to create tables before loading the data. The table structure comes from the annotations that you specify in the mapping schema that XML Bulk Load uses. (See SQL Server Books Online—BOL—for information about the annotations used in mapping schemas.) Annotations in the mapping schema should specify temporary tables rather than tables in your database that the existing stored procedures control. Listing 7 shows the VB code for executing an XML Bulk Load that creates temporary tables. The code uses the same schema as the schema that Listing 8 shows but connects to a database other than Pubs.

To run the VB code, you need to reference the Microsoft XML Bulk Load for SQL Server 1.0 Type Library in your VB project. To add the reference, access the Reference menu item on the Project menu. The type library is installed with XML for SQL Server Web Release 1 (WR1), available at http://www.microsoft.com/sql/downloads/default.asp. Although this solution requires a bit of coding to write the new stored procedures, it's still much simpler than writing your own XML Bulk Load code.