Introduction
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>
<city>Dallas</city><state>LA</state><country>USA</country></publisher></root>";
2) SqlConnection sCn =
new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs");
3) sCn.Open();
4) SqlCommand sCmd = new SqlCommand("MyInsertSP",sCn);
5) sCmd.CommandType = CommandType.StoredProcedure;
6) sCmd.Parameters.Add(@strXML",SqlDbType.VarChar,1000).Value=strXML;
7) sCmd.ExecuteNonQuery();
- 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
2) (
3) @strXML varchar(1000)
4) )
5) AS
6) Begin
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
11)RETURN
12)End
- 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.
Summary:
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.