<!------------------------------- STEP 3 ---------------------------><!-- Add the article text. Please use simple formatting (
Microsoft SQL Server 2000 has a feature "OpenXML" using which we can update a table by calling a Stored Procedure passing an XML String. By this method we can insert a set of rows into a table by making a single connection to Database, which reduces network traffic and also has a better performance over the traditional ways of either calling the insert command for every single row or creating a string which has a delimiter for every row, another delimiter for every column and programmatically loop through the string and retrieve each row and insert into the database.
Method to call a stored procedure using ADO.NET and passing the xml string as a parameter to the procedure is shown below:
1) string strXML = "<root><publisher><pub_id>9986</pub_id><pub_name>vijay</pub_name>
2) SqlConnection sCn =
4) SqlCommand sCmd = new SqlCommand("MyInsertSP",sCn);
5) sCmd.CommandType = CommandType.StoredProcedure;
- Create the XML String that contains the data that has to be inserted into the database.
- Create the Connection object.
- Open the connection to the data source.
- Create a command object (pass the sp name and connection object).
- Set the command type as a Stored Procedure.
- Set up the parameter for the Stored Procedure and assign the XML.
ExecuteNonQuery is used to execute Insert/Update/Delete which doesn’t return any Row.
The SQL Stored Procedure that handles the data insert is given below
1) Create PROCEDURE dbo.MyInsertSP
3) @strXML varchar(1000)
7) Insert into publishers
8) Select * from OpenXml(@intPointer,'/root/publisher',2)
9) With (pub_id char(4), pub_name varchar(40), city varchar(20),
state char(2),9) country varchar(20))
10) exec sp_xml_removedocument @intPointer
- Declare a handle which will hold a pointer to the XML Document.
- Create the Handle to hold the XML Document in SQL Server Memory.
- Open the XML Document and Insert into the Database, Here the OpenXML takes 3 parameters
- the Handle we created for the XML document in memory,
- the XPath to access the various elements of the XML document and
- a flag – here 2 means access the
XPath as elements,
We can also access the XML document with
XPath as attributes if our XML is like
<root><publisher pub_id='9987' pub_name='vijay'></publisher></root>
- Remove the Xml Document from the SQL server Memory.
OPENXML is a new function added to SQL Server 2000. This provides a rowset view over an XML document. OPENXML is a feature by which an XML document can be used similar to relational database format. It allows for the passing of an XML document to a T-SQL stored procedure for updating the tables.