Database :: Managing Database Transactions with OPENXML

Posted 2007-02

SQL Server's OPENXML function is a good example of using XML to your advantage. Among other things, (bulk inserts) it lets you properly manage transactions on the DBMS, rather than trying to do so on the front-end web application. Code follows:

USE pubs

CREATE TABLE openxml_master (
  id            int identity not null primary key,
  customer_id   int not null
)

CREATE TABLE openxml_details (
  order_id    int not null,
  widget_id   int not null,
  widget_qty  int not null
)
GO

Here we set up a simple, but typical, master table - details table relationship. openxml_master tracks customer orders, and openxml_details holds individual entries with quantities of each item purchased. Obviously you don't want any part of the order left out, which leads us to the transaction requirement.

CREATE PROCEDURE openxml_test
  @xml_doc      ntext=NULL
AS
SET NOCOUNT ON
DECLARE @hdoc int, @err int, @order_id int

EXEC @err   = sp_xml_preparedocument @hdoc OUTPUT, @xml_doc
SELECT @err = @@error + coalesce(@err, 119)
IF @err != 0 RETURN @err

sp_xml_preparedocument parses the XML document passed into the stored procedure into a tree structure of XML nodes and stores it in SQL Server memory. We then do error checking on sp_xml_preparedocument's return value.

BEGIN TRANSACTION
-- 'master' table
  INSERT INTO openxml_master (customer_id)
    SELECT id FROM OPENXML(@hdoc, '/root/customer', 0)
    WITH (
      id    int
    )
  SELECT @order_id = @@identity
  SELECT @err=@@error IF @err!=0 BEGIN ROLLBACK TRANSACTION RETURN END

-- 'details' table
  INSERT INTO openxml_details (order_id,widget_id,widget_qty)
    SELECT @order_id,widget_id,widget_qty
    FROM OPENXML(@hdoc, '/root/customer/row', 0)
    WITH (
      widget_id   int,
      widget_qty  int
    )
  SELECT @err=@@error IF @err!=0 BEGIN ROLLBACK TRANSACTION RETURN END
COMMIT TRANSACTION

@hdoc, the first parameter passed to OPENXML, is the OUTPUT parameter ("handle") from sp_xml_preparedocument. Basically, it gives you access to the XML document. The second parameter passed to OPENXML is a XPATH statement. And the third parameter simply tells SQL Server to map node attributes to the columns of each row. In between we do error checking, making sure to rollback the transaction on any error.

-- clean up memory from XML document
  EXEC sp_xml_removedocument @hdoc
GO

The documentation is very specific regarding the need to call sp_xml_removedocument to free memory.

EXEC openxml_test N'
<root>
<customer id="1">
<row widget_id="1" widget_qty="1" />
<row widget_id="2" widget_qty="2" />
<row widget_id="3" widget_qty="3" />
</customer>
</root>
'
SELECT * FROM openxml_master
SELECT * FROM openxml_details

DROP TABLE openxml_master
DROP TABLE openxml_details
DROP PROCEDURE openxml_test

Execute the stored procedure and drop the objects, which gives the expected results:

id          customer_id 
----------- ----------- 
1           1

(1 row(s) affected)

order_id    widget_id   widget_qty  
----------- ----------- ----------- 
1           1           1
1           2           2
1           3           3

(3 row(s) affected)

One thing to watch out for is that the text passed to the stored procedure must be a well-formed XML document.

Code

Other