MS SQL Stored procedure with XML

I find that it's easiest to visualize if you start with a very simple XML document. Here is an example XML snippet:

Here is how that stored procedure would be written:

alter procedure usp_InsertManyRows

@XMLDOC varchar(8000)

AS

declare @xml_hndl int

--prepare the XML Document by executing a system stored procedure

exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC

--insert into table

Insert Into testtable

(

InsertedID

)

Select

IDToInsert

From

OPENXML(@xml_hndl, '/items/item', 1)

With

(

IDToInsert int '@id'

)

Now follows a comparison of how each performs. The following tests were run with simple ASP.NET pages using a local SQL Server database. The results are quite dramatic, and would only be more dramatic if the hits had to go over the network.