- The OPENXML Function in Transact-SQL
- Joining OPENXML Output with Other Tables
- Advantages of Using OPENXML
- Performance Issues With OPENXML
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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