Insert Data into SQL 2000 Using OpenXML & ADO.NET

• Download source in C# - 1kb
• Download source in VB.NET - 1kb
• Download source SQL - 1kb

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();

1. Create the XML String that contains the data that has to be inserted into the database.
2. Create the Connection object.
3. Open the connection to the data source.
4. Create a command object (pass the sp name and connection object).
5. Set the command type as a Stored Procedure.
6. Set up the parameter for the Stored Procedure and assign the XML.
7. 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

7. Declare a handle which will hold a pointer to the XML Document.
8. Create the Handle to hold the XML Document in SQL Server Memory.
9. 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>

10. 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.