Bulk data transactions using OpenXML

OPENXML is a new function added to SQL Server2000 that provides a rowset view over an XML document. Since a rowsetis simply a set of rows that contain columns of data, OPENXML is thefunction that allows an XML document to be treated in the familiarrelational database format. It allows for the passing of an XMLdocument to a T-SQL stored procedure for updating the data.

OpenXML ? to summarize

? It extends the SQL Language
? It is used within T-SQL Stored Procedures
o XML Document passed as parameter
? It uses row and column selectors utilizing XPath
? It supports the following:
o Attribute and element-centric mappings.
o Edge table rowset.
o XML annotation/overflow column.
o Hierarchy support.

OpenXML ? Syntax

? OpenXML(idoc, rowpattern,flags)

[WITH (SchemaDecl | Tablename)]

? Parameters

? idoc

Is the document handle of the internal representation of an
XML document. The internal representation of an XML document
is created by calling sp_xml_preparedocument.

sp_xml_preparedocument – Reads the Extensible Markup Language
(XML) text provided as input, then parses the text using the MSXML
parser (Msxml2.dll), and provides the parsed document in a state
ready for consumption. This parsed document is a tree representation
of the various nodes (elements, attributes, text, comments, and so
on) in the XML document.

sp_xml_preparedocument returns a handle that can be used to access
the newly created internal representation of the XML document

? Rowpattern

Is the XPath pattern used to identify the nodes (in the XML document
whose handle is passed in the idoc parameter) to be processed as rowsflags

? Flags

Indicates the mapping that should be used between the XML data and
the relational rowset, and how the spill-over column should be filled.
flags is an optional input parameter, and can be one of these values

? SchemaDecl

o in-line meta-data for relational view(column_name1 column_type1
[colpattern1], ?, column_namej column_typej [colpatternj])

? Tablename

o existing table to obtain meta-data for relational view

? Edgetable

o if neither SchemaDecl or Tablename is specified

Architecture:

The following part of the article describesthe usage of OPENXML function to insert multiple rows of data in asingle database call. This can be an effective alternative to loopingthrough an array and calling a stored procedure to insert a row eachtime.

The example provided inserts 10 rows into atable, so the OPENXML approach is cutting the database calls from 10 to1 in this case. This minimization of database calls can translate intosignificant performance and scalability benefits. Each time a databasecall is made, network and database resources are utilized. The moredemands you make for these resources, the more likely you willexperience degradation in your application?s performance. OPENXMLenables you to, in essence, package data together in a single call (asXML), map it to a rowset view, and execute all of the inserts withinthe same database call which results in a minimization of theutilization of these resources.

CREATE PROC sp_insert_employee @empdata ntext
AS
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @empdata
INSERT INTO Employee
SELECT *
FROM OPENXML(@hDoc, '/Employee')
WITH Employee
EXEC sp_xml_removedocument @hDoc
GO

You can see that the only parameter passed tothe procedure is the XML passed as a varchar. Depending on the size ofthe XML string you are working with, the XML string input parameter canbe (n)char or (n)text in addition to (n)varchar. The @hDoc variable isrequired by the sp_xml_preparedocument as an outputparameter.Sp_xml_preparedocument is a SQL Server system storedprocedure that creates an internal representation of the XML documentpassed to it, and returns this document handle in @hDoc.

The OPENXML function accepts three arguments,the first two of which are required. The first argument is the documenthandle that you created by calling sp_xml_preparedocument. This tellsOPENXML which XML document you are working with. The second argument isan XPATH (XML Path Language) pattern used to identify the nodes in theXML document.

Each node identified by the XPATH patterncorresponds to a single row in the rowset generated by OPENXML. In ourexample, there are 10 < Employee> nodes each representing a rowin the rowset. The third argument is optional and specifies how themapping should occur between the rowset created by OPENXML and the XMLdocument. The default is attribute-centric, which means XML attributesof a given name are stored in a column in the rowset with the same name

The WITH clause allows you to specify a Schemadeclaration (to specify additional mapping between a column in therowset and a value in the XML document) or the table name if the tablealready exists with the desired schema.

The example does a simple insert into theEmployee table, and since the XML document was created specifically toinsert multiple rows into the Employee table, it is sufficient tospecify the table name Employee in our WITH clause.

The last statement, EXEC sp_xml_removedocument@hDoc, is called to remove the XML document from it?s storage locationin the internal cache of SQLServer.

In summary, the new OPENXML function in SQLServer 2000 can be useful for processing multiple table inserts withina single database call. The ability to map an XML document to a rowsetrepresentation of a specified portion of the XML document within astored procedure can maximize the efficiency with which repetitive typeinserts are accomplished.

You can also update and delete rows with XML using OPENXML. Without going into specifics the process is basically:

1. Create an internal representation of the XML document with SP_XML_PREPAREDOCUMENT
2.Perform the UPDATE / DELETE using the FROM OPENXML () WITH … syntax,referencing the internal representation of the XML document
3. Destroy the internal representation of the XML document with SP_XML_REMOVEDOCUMENT An example of how to use OPEN XML for updating/deleting records is given below:

CREATE PROC sp_update_employee @empdata ntext
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@empdata
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@hDoc, '/root/Employee')
WITH Employee XMLEmployee
WHERE Employee.eid = XMLEmployee.eid
EXEC sp_xml_removedocument @hDoc
SELECT *
from Employee
FOR XML AUTO

CREATE PROC sp_delete_data @empdata ntext

AS
DECLARE @hDoc int
exec sp_xml_preparedocument @idoc OUTPUT, @doc
DELETE Customers />FROM O
PENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '@OrderID',
CustomerID varchar(10) '@CustomerID',
OrderDate datetime '@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity') b
WHERE Customer.CustomerID=b.CustomerID
EXEC sp_xml_removedocument @idoc

Summary

1. Leverages existing relational model for use with XML

2. Provides:
?A mechanism for updating database with data in XML format
? Multi-row updates in single stored procedure call
? Multi-table updates leverage XML hierarchy
? Queries that join existing tables with XML data

Authors

Ravi Chander Gunishetti
Email id – ravichander.gunishetti@wipro.com

Harikishan Jayaraj
Email id – harikishan.jayaraj@wipro.com