For SQL 2005, I would use SQL's built in XML processing. You can pass
the xml text in as a parameter to a stored procedure.

Here's a snippet from what I have done with something similar:

/*
declare @XmlDoc nvarchar(4000)
set @XmlDoc =
'<patient>
    <answer>
        <PatientID>13303</PatientID>
        <QuestionID>200</QuestionID>
        <AppointmentID>105084</AppointmentID>
        <AnswerValue>217,218</AnswerValue>
        <SaveTypeID>1</SaveTypeID>
    </answer>
</patient>'

exec up_PatientAnswer_Save_FromXml @XmlDoc = @XmlDoc
*/
ALTER procedure [dbo].[up_PatientAnswer_Save_FromXml]
(
    @XmlDoc ntext
)
as
set nocount on

declare @Answers table
(
    AnswerID Int identity,
    PatientID Int,
    QuestionID Int,
    AppoIntmentID Int,
    SaveTypeID Int,
    AnswerValue nvarchar(3900) null,
    AnswerValueDate datetime null,
    AnswerValueInt Int null
)   

declare @Pointer Int

-- get a pointer to the parsed doc
exec sp_xml_preparedocument @Pointer output, @XmlDoc

-- insert Into the table var so we can dispose of the xml doc
-- and work with the data easier (better)
insert Into @Answers
    (PatientID, QuestionID, AppointmentID, SaveTypeID, AnswerValue,
    AnswerValueDate, AnswerValueInt)
select
    PatientID, QuestionID, AppointmentID, SaveTypeID, AnswerValue,
    AnswerValueDate, AnswerValueInt
from
    openxml(@Pointer, '/patient/answer', 2)
with
    (   
        PatientID int,
        QuestionID int,
        AppointmentID int,
        SaveTypeID int,
        AnswerValue nvarchar(3900),
        AnswerValueDate datetime,
        AnswerValueInt int
    )

-- release the xml doc
exec sp_xml_removedocument @Pointer

-- processing from here is done with the data in the table variable

More info can be found here:

http://www.perfectxml.com/articles/xml/openxml.asp

If the XML that you receive is usable as is, you can just pass that
along. Or you can create new XML that is in a format that you expect.

You may also be able to us SqlBulkCopy to insert the data into a loading
table and have a stored procedure that works off of that table.

In SQL 2008, you will be able to pass a DataTable as a parameter to a
stored proc.

On Mar 4, 8:27 pm, mj2...@yahoo.com wrote:

SQL Server MVP Erland Sommarskog wrote an excellent article "Arrays
and Lists in SQL". We use his approach. We pack up to 100K values in
an image, that mimics an array. We can use several images. Performs
very fast.

I still think XML may be your best option, but preload the XML into a single
table and then run processes on the data there. This works best if you can
create a table to load. You may need multiple load tables if you have
different clients. This will still hammer the server for a bit. It should
not be an issue for thousands of records, however. After you fill one table,
you need to get its primary key values into the load table, especially if
using row guids or IDENTITY.

When you get to millions of records, this can still cause issues with the
server. In these cases, you may want to preprocess outside of SQL Server. If
you opt for this, you essentially create bcp files (or files for
BULK_INSERT) outside of SQL. When you get to the step to create IDENTITY
values, you can use DBCC commands to increment a "hole" the size of the
records being loaded. The customer's unique key and new IDENTITY driven
primary key can be loaded into a hashtable. Load time for a million records
is about 2-3 seconds with 1 million lookups takign far less than a second.
At least this was the stats we worked with when I did a project that
preprocessed offline.

If you can load into a single table from XML and then run commands, it is
less time consuming to code.

What about batching? It is a possibility, but realize you are just
flattening the performance curve. Rather than a single huge hit, you are
taking many smaller hits. This could well be the solution you are looking
for, but you may find that this approach fails as you grow in size. Of
course, you can always move SQL Server to a big box, the data files to a
SAN, etc. to alleviate some, if not all, issues. At least until you grow to
millions of files being loaded this way.

Another potential option is getting more transactional with the process that
creates the XML so single records are sent. If this is a client, it may not
be an option. Even internal, there may be valid reasons not to go record by
record.

Also, in most of these systems, you have to check for existing records
before loading, as inevetably someone sends you the same info. You may have
some system of checks and balances here to avoid this. If not, consider it,
as a process failure in the middle of a large batch of records can be
extremely hard to cure.

*************************************************

I vote YES for xml.

You need to look at the *non* OPENXML option though, and test it.

http://blogs.sqlservercentral.com/michael_coles/archive/2008/01/20/stop-using-op
enxml-please.aspx

which takes the @xml XML datatype in 2005/2008.
I have not personally vouched for the claims made in the above URL..just
sending you the info/link.

and check here
http://www.sommarskog.se/arrays-in-sql-2005.html#XML