Click here to Skip to main content
Click here to Skip to main content

Insert Data into SQL 2000 Using OpenXML & ADO.NET

, 1 Jul 2003
Rate this:
Please Sign up or sign in to vote.
An example of inserting data into SQL Server using OpenXML + ADO.NET
<!------------------------------- STEP 3 ---------------------------><!-- Add the article text. Please use simple formatting (

,

etc) -->

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

 <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

Share

About the Author

No Biography provided

Comments and Discussions

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

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141030.1 | Last Updated 2 Jul 2003
Article Copyright 2003 by S. Vijaya Raghavan
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid