Home > Articles > Data > SQL Server

  • Print
  • + Share This
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


  • + Share This
  • 🔖 Save To Your Account