Performance Issues With OPENXML
There is a slight performance penalty when using OPENXML. sp_xml_preparedocument and sp_xml_removedocument both execute the parsing through an extended stored procedure, so parsing is actually as efficient as it gets. However, as you might imagine, going through a ton of XML over and over again can consume some resources. If you need to use the output of the OPENXML function several times in your procedure, make a note of this trick. Instead of using the OPENXML function over and over again, use it once, and populate a table variable. Then use the table variable to join to other tables, perform INSERT, UPDATE, and DELETE statements (or whatever else you desire). Using a table variable is a lot more efficient than parsing XML repeatedly.
To demonstrate the above principle, I modified the procedure in the previous example slightly. In addition to UPDATING the authors table, the procedure also populates the fictitious audit table to keep track of the changes:
ALTER PROCEDURE update_authors_OPENXML ( @xml_text TEXT) AS SET NOCOUNT ON -- document handle: DECLARE @i INT -- create internal representation -- of the document EXEC sp_xml_preparedocument @i OUTPUT, @xml_text -- table variable to hold output of OPENXML: DECLARE @authors TABLE ( au_id id NOT NULL, au_lname VARCHAR(40) NULL, au_fname VARCHAR(20) NULL, phone CHAR (12) NULL, address VARCHAR(40) NULL, city VARCHAR(20) NULL , state CHAR (2) NULL , zip CHAR (5) NULL , contract BIT NOT NULL ) -- populate the table variable: INSERT @authors ( au_id, au_lname, au_fname, phone, address, city, state, zip, contract) SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM OPENXML(@i, '/root/authors') WITH authors -- check for errors IF @@ERROR <> 0 BEGIN RAISERROR('error occured while using OPENXML', 16, 1) RETURN END -- cleanup: EXEC sp_xml_removedocument @i BEGIN TRAN -- populate audit: INSERT audit ( table_changed, key_of_the_changed_table, changed_by, date_changed) SELECT 'authors', au_id, USER_NAME(), GETDATE() FROM @authors a INNER JOIN authors b ON a.au_id = b.au_id IF @@ERROR <> 0 BEGIN RAISERROR('error occured while populating audit trail', 16, 1) ROLLBACK RETURN END -- update authors: UPDATE authors SET au_fname = b.au_fname, au_lname = b.au_lname, address = b.address, phone = b.phone, city = b.city, state = b.state, zip = b.zip, contract = b.contract FROM authors a INNER JOIN @authors ON a.au_id = b.au_id IF @@ERROR <> 0 BEGIN RAISERROR('error occured while updating authors', 16, 1) ROLLBACK RETURN END COMMIT TRAN -- return success RETURN 0