Using XML To Send Multiple Rows At Once Into A Stored Procedure

From time to time, people ask the question "How can I insert many records at once into SQL Server?"  The purpose of this quick article is to describe that process in very basic details.  I'll give a very simple example, so that you can then take it from there.

 

I find that it's easiest to visualize if you start with a very simple XML document.  Here is an example XML snippet:

 

<items>

            <item id="1"></item>

            <item id="4"></item>

            <item id="12"></item>

            <item id="112"></item>

            <item id="234"></item>

            <item id="605"></item>

</items>

 

Let's say you have a checkboxlist, and you want to send the list of "checked" items into a SQL Server stored procedure, making an INSERT for each checked item.  You want to create a string containing text that is formatted as above, and for each checked item, insert a "row."  Here is a simple class in VB.NET to abstract that functionality:

 

Public Class XMLItemList

    Private sb As System.Text.StringBuilder

 

    Public Sub New()

        sb = New System.Text.StringBuilder

        sb.Append("<items>" & vbCrLf)

    End Sub

 

    Public Sub AddItem(ByVal Item As String)

        sb.AppendFormat("<item id={0}{1}{2}></item>{3}", Chr(34), Item, Chr(34), vbCrLf)

    End Sub

 

    Public Overrides Function ToString() As String

        sb.Append("</items>" & vbCrLf)

        Return sb.ToString

    End Function

 

End Class

 

As you can see, there is nothing special or fancy going on here.  The purpose of the class is just to make the next example easier to understand:

 

Dim MyXMLItemList As New XMLItemList

For i As Integer = 0 To MyCheckBoxList.Items.Count - 1

    If MyCheckBoxList.Items(i).Selected = True Then

        MyXMLItemList.AddItem(MyCheckBoxList.Items(i).Value)

    End If

Next

 

Given the above, you can use the MyXMLItemList.ToString method to get a string representation that is formatted like the XML snippet described in the beginning.

 

All that remains then is to call your stored procedure, passing in the string produced above as a parameter.  Here is how that stored procedure would be written:

 

alter procedure usp_InsertManyRows

@XMLDOC varchar(8000)

AS

 

declare @xml_hndl int

 

--prepare the XML Document by executing a system stored procedure

exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC

 

 

--insert into table

Insert Into testtable

            (

            InsertedID

            )

Select

            IDToInsert

From

            OPENXML(@xml_hndl, '/items/item', 1)

            With

                        (

                        IDToInsert int '@id'

                        )

 

 

Now follows a comparison of how each performs.  The following tests were run with simple ASP.NET pages using a local SQL Server database.  The results are quite dramatic, and would only be more dramatic if the hits had to go over the network:

 

First, I created a simple page that hit the database 10 times, each time inserting one record.  Here are the results of that test:

            97 Requests Per Second

            28254 hits in 5 minutes

            282540 records inserted

           

Next, I created a simple page that hits the database only once, sending in all ten records to be inserted together at once, using the XML format mentioned in this article.  Here are the results of that test:

            186 Requests Per Second

            55349 hits in 5 minutes

            553490 records inserted

 

Quite an impressive difference!

 

Print | posted on Tuesday, January 04, 2005 11:53 AM

Feedback

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Mike Swaim at 1/4/2005 1:38 PM Gravatar
2 things

1) What's the performance of doing 10 inserts/request? (I believe that MS is looking into doing something like that with datasets, to speed up updates.)

2) If you're using Oracle, you can pass arrays to SQL statements. It's a cleaner and faster implementation of what you're doing above, especially with large amounts of data. (I was using 10,000 row arrays, doing inserts whenever they filled up.)

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Travis Laborde at 1/4/2005 1:57 PM Gravatar
Mike, I'm not sure I understand your question. The two test scenarios that I ran were:

1) Where the web page actually hit the database 10 times, once for each record to be inserted. So, one "hit" of the webpage would cause 10 "hits" to the database, for 10 records to be added.

2) Where the web page only hit the database once, sending in the XML snippet as one parameter, which was then parsed by the stored procedure. So, one "hit" to the webpage would cause only one "hit" to the database, for 10 records to be added.

The performace differences were listed at the end of the article.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Mike Swaim at 1/4/2005 4:18 PM Gravatar
One of the tricks to improve SQL Server performance is to concatenate multiple commands per round trip to the database. So issuing "insert into foo values(1);insert into foo values(2);insert into foo values(3)" is noticably more efficent than
"insert into foo values(1)", "insert into foo values(2)", "insert into foo values(3)".

I'm curious whether simply concatenating SQL commands is faster or slower than using XML and going through a stored procedure.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Travis Laborde at 1/5/2005 8:31 AM Gravatar
Mike - You are correct. I do have two small issues with that method though:

First, I'm sure that multiple inserts would not perform as well as the INSERT SELECT process. But, I'll test that, and get back to you...

Second, I have personally given up on dynamic SQL, in favor of stored procedures everywhere. It's certainly beyond my ability or desire to argue the case, but I'm sure that a quick search will find you many articles published, in the last year alone, on that topic. Though I know that there are "safe" ways to do it, in my experience it is very typical for an ASP developer to do dynamic SQL in a very bad way, susceptible to injection attacks, etc.


Todd - thanks for your comments as well. I agree Erland has a great article there. Obviously, much less was put into my article. I was not intending to show the BEST way to send an array of data into SQL Server, but rather to show ONE way, a very SIMPLE and easy to understand way, that can be expanded upon with a lot of flexibility.

It's not about the code, it's about the concept. People think "I've got this series of checked items and I want to send them all to the database at once." I think this was a very clear beginning tutorial on the OPENXML command in TSQL. I appreciate that you use a different method.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Mike Swaim at 1/16/2005 4:28 AM Gravatar
I've decided that I don't like SPs for the following reasons.
1)For simple statements, it roughly doubles your code. (You still have the same amount of code on the client, and you now also have the SP code.)
2)For complicated one-off queries, I like to see the SQL with the code that's calling it. Also, if the query ever needs to change, you have to create a new SP, or risk breaking existing code.
3) I had a very bad experience once where a SP's signature changed, and it was missed in the client code. The bad thing was that it worked 99% of the time. Tracking it down was horrible.

Anyway, typically the only times than I need to build SQL is when I don't know what's going to be in my where clause until run time.
And I was shocked when I found out that people were having problems with SQL injection. Coming from PB/Delphi/C++, I assumed that everybody used parameterized SQL. Why don't ASPers?

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by vistor at 6/17/2005 10:05 AM Gravatar
The greatest thing about string formatting in .net is reusability:

"<item id={0}{1}{2}></item>{3}", Chr(34), Item, Chr(34), vbCrLf

Could be:

"<item id={0}{1}{0}></item>{2}", Chr(34), Item, vbCrLf

The whole point of string formatting is to make the code easier to read... Good article.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Jude at 6/20/2005 8:37 AM Gravatar
Can you help me?

I am passing huge changed data (diffgram) of the dataset to ORACLE database in the format of XML. But when the size is exceeding the arround 2000 character the oracle is throwing a error

ORA-01460: unimplemented or unreasonable conversion requested SOURCE : System.Data.OracleClient

But i have declared the parameter type as CLOB.


Can you please help me out?


regards,
Jude

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Jude at 6/20/2005 8:40 AM Gravatar
<font color=blue>Can you help me?

I am passing huge volume of data - changed data (diffgram) of the dataset- to ORACLE Stored Procedure in the format of XML. But when the size is exceeding arround 2000 character, oracle is throwing an error

ORA-01460: unimplemented or unreasonable conversion requested SOURCE : System.Data.OracleClient

But i have declared the parameter type as CLOB.


Can you please help me out?


</font>

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Ravi Gupta at 7/19/2005 7:15 AM Gravatar
Can you provide a sample Oracle stored procedure receiving XML as input and parsing the XML to do the insert.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Jude at 8/6/2005 2:55 AM Gravatar
Hi All,
I have done it. Now I am able to pass XML data upto 4GB on single trips.

Thanx to all of you.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by rick at 9/1/2005 5:53 PM Gravatar
How did you do it on the stored proc side? What param type are you using ? CLOB?

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Affi at 9/15/2005 1:23 AM Gravatar
Hi All,
I have to insert Multiple rows of record in Multiple Tables can u help me on this.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Tej at 10/2/2005 1:07 PM Gravatar
Hi Jude!
I am also trying to pass in huge data in XML to an oracle stored procedure. While trying to do so I am getting the following error.

ORA-01460: unimplemented or unreasonable conversion requested

Can you please let me know how to resolve it?
Thanks in advance...
Tej

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Tej at 10/3/2005 7:24 AM Gravatar
Hi all,

I am trying to pass in large data in XML from a VB component(vb 6.0) to an oracle stored procedure.With in the Oracle Stored procedure, I am parsing the data in XML and inserting the records into the database row by row. Pass in Parameter in VB method is of Type "adLongVarChar" and in Oracle SP the IN Parameter type is "Long".
When I tried with a small XML data samples, it is working fine. When I tried with a huge volume of XML data(1 MB) it is throwing the following error.

ORA-01460: unimplemented or unreasonable conversion requested

Can any body help me in resolving this problem?

Judy! I am very curious, you were able to pass XML data upto 4GB on single trips. It will be great if you let me know how did you do this.

Thanks to all of you.

Tej

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Pic at 10/24/2005 10:33 PM Gravatar
Jude,

We are running into a similar problem trying to pass large XML into Oracle procs but run into the 32k limit on stored proc parameters. I'd love to know how you got a proc to take 4gb.

Pic

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Suj at 11/6/2005 12:13 AM Gravatar
Can you please give the code in C# ?

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Suj at 11/6/2005 5:01 AM Gravatar
What is the equivalent of Chr(34), Item, Chr(34), vbCrLf in C#? Can any one reply please.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Chris at 2/3/2006 10:32 AM Gravatar
I am having the same problem with Oracle. Did anybody get a response on how to do this?

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Nishant at 2/7/2006 5:38 AM Gravatar
Hi,I amde the General SP for Bulk insert with the help of Dynamic SQl but i am getting an error that error in converting varchar daatatype to type of int.When i did Cast and Convert it removes the error but openXML first parameter require int datatye which is now changed to varchar so m getting an another error.i am unable to understande what shoul i do

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Suraj at 3/23/2006 1:30 PM Gravatar
Can anyone give idea how who were able to pass 4gb of data to oracle DB through XML/clob.

Thanks

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by zap at 4/23/2006 4:47 AM Gravatar
Hi Iam getting problem while passing xmldata to
Stored procudure i dont know whats going on . Data not inserted into the table
My Sql Stored procedure is
CREATE procedure dbo.AddressMastAdd(@xmlDoc varchar(8000)) as
DECLARE @hDoc int,
@NSEndPos int,
@err int

EXEC @err = sp_xml_preparedocument @hdoc OUTPUT, @xmlDoc
SELECT @err = @@error + coalesce(@err, 4711)
IF @err <> 0 RETURN @err
insert into AddressMast(Classid,Formid,Addid,add1,city,zip,countryid)
select *
from openxml (@hdoc, 'NewDataSet/Tabel1',1)
with (Classid int '@classid',
Formid int '@Formid',
Addid int '@addid',
add1 nvarchar(50)'@add1',
city nvarchar(25)'@city',
zip char(10) '@zip',
countryid int '@countryid' )
EXEC sp_xml_removedocument @hdoc

and my xml file is

<NewDataSet>\r\n <Table1>\r\n <CLASSID>1</CLASSID>\r\n <FORMID>58</FORMID>\r\n <ADDID>1</ADDID>\r\n <ADD1>address1</ADD1>\r\n <CITY />\r\n <ZIP />\r\n <COUNTRYID>1</COUNTRYID>\r\n </Table1>\r\n</NewDataSet>

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by JUDE at 6/11/2006 4:32 AM Gravatar
Hi All,
I am sorry. I am here after a long time. If you are facing the same problem still then please post your request and also send a mail to judestephen@buildersv2b.com


regards,
Jude

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Vaishali Shah at 6/14/2006 10:47 AM Gravatar
I need to pass similar XML input to stored procedure. You wrote that you were able to do it. How did you manage 4GB? I am trying to call this proc from my j2EE app.

Thank you

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by kumar at 6/19/2006 6:58 AM Gravatar
Hi

this article is good and it is showing how v can imporve the application performance.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by AA at 7/6/2006 4:13 AM Gravatar
Tej,
Could u please share the stored procedure code for bulk update to oracle datatable using XML.
It is very urgent. please do help.
Thanks in advance.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Rizzo at 7/10/2006 1:51 AM Gravatar
SET NOCOUNT ON
INSERT tbl (someID, [somevalue]) VALUES (2,72);
INSERT tbl (someID, [somevalue]) VALUES (22,71);
INSERT tbl (someID, [somevalue]) VALUES (212,75);
INSERT tbl (someID, [somevalue]) VALUES (223,72);
...
etc..
GO

and got very-very-very fast Insert of rows....

Just parse it using StringBuilder, loop the data as much as you want up to the limit. Use Threading background while processing if you don't want to feel the neck, cast it into string then inject the sql statement. Done!

Trust me.... simple and very fast. Even the adapter.Update() cannot beat the sql string statement speed.

If you think of inserting millions of data... what??? use clustering of 4 SQL Server/Oracle, make it 10 Server if want more faster, split the INSERT statement into 4 or 10 parts... than you get the speed right. If not, don't bother to wait. hmmm... I wonder how long does the insert statement will last.

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Kaiser at 8/2/2006 12:57 PM Gravatar
Jude I have sent you an email to check same issue

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by iris at 9/26/2006 4:01 PM Gravatar
I also have a problem with passing a large xml clob to my store procedure
if anyone solved this issue please email me
at iris.abramovitz@mullintbg.com
Thanks

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure 8/6/2005 2:55 AM Jude

left by prakash patil at 12/7/2006 11:29 PM Gravatar
I need to pass XML file as input to stored procedure. You wrote that you were able to do it. How did you manage 4GB? plz mail me

# re: Using XML To Send Multiple Rows At Once Into A Stored Procedure

left by Anil Kumar at 1/11/2007 1:35 AM Gravatar
Perhaps, This is the best article i have ever seen on the net about sending multiple parameters as xml to Stored Procedure.
Can any one of you guys publish the code of the
Application, StoredProc to demonstrate this on Northwind DB?
Say, i have 5 rows in DataTable.i want to send all 5 rows once to the sp.how can i do that.
Comments have been closed on this topic.