Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server
Article

Insert Data into SQL 2000 Using OpenXML & ADO.NET

Rate me:
Please Sign up or sign in to vote.
4.59/5 (12 votes)
1 Jul 20032 min read 144K   3.4K   54   6
An example of inserting data into SQL Server using OpenXML + ADO.NET

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:

C#
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

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

XML
 <root><publisher pub_id='9987' pub_name='vijay'></publisher></root>
  1. 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Unknown
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalgetting error when try to insert Pin
Member 283601212-Sep-08 0:55
professionalMember 283601212-Sep-08 0:55 
GeneralNot inserting more than one records at a time Pin
alav10-Jan-07 3:50
alav10-Jan-07 3:50 
GeneralPassing data from xmlfile Pin
meverts6-Dec-05 8:53
meverts6-Dec-05 8:53 
GeneralWorks fine, but it is not fast enough Pin
Ondrej Novotny5-Jan-04 3:33
Ondrej Novotny5-Jan-04 3:33 
GeneralRe: Works fine, but it is not fast enough Pin
Tim McCurdy24-May-06 2:50
Tim McCurdy24-May-06 2:50 
GeneralRe: Works fine, but it is not fast enough Pin
Pratik.Patel16-Oct-08 0:37
Pratik.Patel16-Oct-08 0:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.