- 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
Advantages of Using OPENXML
Perhaps the best thing about OPENXML is that it lets you pass a bunch of rows to the stored procedure in an easily decipherable format. This way, you shove all the rows you want to INSERT, UPDATE, or DELETE into a string; send it to a stored procedure in a single call; and you're done! The stored procedure can handle the parsing and data modification on its own. Compare that with going across the wire thousands of times for each individual UPDATE, and you get the picture.
It is a good practice to enclose all of your OPENXML functions inside of stored procedures. Stored procedures can accept parameters with the TEXT data type, which can have a maximum length of 2,147,483,647 characters. T-SQL variables, on the other hand, can hold only up to 8000 characters—the upper limit for VARCHAR. You cannot declare a variable with TEXT data type. Therefore, always use OPENXML inside stored procedures if you expect to pass large XML documents from the front end or middle tier components to the database.
Following is an example of an UPDATE procedure using the XML as an input parameter. This procedure parses the XML document, and updates the authors table accordingly:
CREATE 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
-- run an update:
UPDATE authors
SET au_lname = b.au_lname,
au_fname = b.au_fname,
address = b.address,
city = b.city,
state = b.state,
zip = b.zip,
contract = b.contract
FROM authors a INNER JOIN
OPENXML(@i, '/root/authors')
WITH authors b
ON a.au_id = b.au_id
-- check for errors
IF @@ERROR <> 0
BEGIN
RAISERROR('error occured while updating authors', 16, 1)
RETURN
END
-- cleanup:
EXEC sp_xml_removedocument @i
-- return success
RETURN 0
I can execute the procedure created by the above script as follows:
EXEC update_authors_OPENXML '
<root>
<authors
au_id="172-32-1176"
au_lname="Whiter"
au_fname="Johnson"
phone="408 496-7223"
address="10932 Bigge Rd."
city="Menlo Park"
state="CA"
zip="94025"
contract="1"/>
</root>'
After executing this procedure, the affected row looks as follows:
SELECT * FROM authors WHERE au_id = '172-32-1176'
Results:
|
au_id |
au_lname |
au_fname |
phone |
Address |
city |
state |
zip |
contract |
|
172-32-1176 |
Whiter |
Johnson |
408 496-7223 |
10932 Bigge Rd. |
Menlo Park |
CA |
94025 |
1 |