This is my bread and butter method now.  2000 = OPENXML.

2005 has some different xml functions.  Those should be explored.

..

One of the benefits is that indexes are updated after the entire set of data is inserted or updated.

Another is a "all or nothing" insert or update.  You don't orphan any data.

I've added a twist, where I can insert or update, based on the match of a primary key.

Here is an example:

if exists (select * from sysobjects

 where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)

 drop procedure uspTitleUpdate

GO

CREATE  PROCEDURE dbo.uspTitleUpdate (

 @xml_doc TEXT ,

 @numberRowsAffected int output  --return

)

AS

SET NOCOUNT ON

DECLARE @hdoc INT -- handle to XML doc

DECLARE @errorTracker int -- used to "remember" the @@ERROR

DECLARE @updateRowCount int

DECLARE @insertRowCount int

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc

-- build a table (variable table) to store the xml-based result set

DECLARE @titleupdate TABLE (

 identityid int IDENTITY (1,1) ,

title_id varchar(6) ,

title varchar(80) ,

type varchar(32) ,

pub_id varchar(32) ,

price money ,

advance money ,

royalty varchar(32) ,

ytd_sales varchar(32) ,

notes TEXT ,

pubdate datetime ,

--used to differeniate between existing (update) and new ones (insert)

alreadyExists bit DEFAULT 0

)

--the next call will take the info IN the @hdoc(with is the holder for

@xml_doc), and put it IN a variableTable

INSERT @titleupdate

 (

  title_id ,

  title ,

  type ,

  pub_id ,

  price ,

  advance ,

  royalty ,

  ytd_sales ,

  notes ,

  pubdate ,

  alreadyExists

)

SELECT

 title_id ,

 title ,

 type ,

 pub_id ,

 price ,

 advance ,

 royalty ,

 ytd_sales ,

 notes ,

 dbo.udf_convert_xml_date_to_datetime (pubdate) , --<<UDF to convert xmldate to tsql date

 0

FROM

 -- use the correct XPath .. the second arg ("2" here) distinquishes

 -- between textnode or an attribute, most times with

 --.NET typed datasets, its a "2"

 --This xpath MUST match the syntax of the DataSet

 OPENXML (@hdoc, '/TitlesDS/Titles', 2) WITH (

 title_id varchar(6) ,

 title varchar(80) ,

 type varchar(32) ,

 pub_id varchar(32) ,

 price money ,

 advance money ,

 royalty varchar(32) ,

 ytd_sales varchar(32) ,

 notes TEXT ,

 pubdate varchar(32) ,

 alreadyExists bit)

EXEC

sp_xml_removedocument @hdoc

--select * from @titleupdate

--lets differeniate between existing (update) and new ones (insert)

Update @titleupdate

SET

 alreadyExists = 1

FROM

 @titleupdate tu , titles

WHERE

 --this where clause is a little weird, usually you'll must match

 --primary key (int or global identifiers)

 ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))

SET NOCOUNT OFF

Update

 titles

set

 title = tu.title ,

 type = tu.type ,

 pub_id = tu.pub_id ,

 price = tu.price ,

 advance  = tu.advance ,

 royalty  = tu.royalty ,

 ytd_sales  = tu.ytd_sales ,

 notes  = tu.notes ,

 pubdate  = tu.pubdate

FROM

 @titleupdate tu , titles

WHERE

 ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))

 AND

 tu.alreadyExists <> 0

Select @updateRowCount = @@ROWCOUNT

INSERT INTO titles

 (

  title_id ,

  title ,

  type ,

  pub_id ,

  price ,

  advance ,

  royalty ,

  ytd_sales ,

  notes ,

  pubdate

)

Select

 title_id ,

 title ,

 type ,

 pub_id ,

 price ,

 advance ,

 royalty ,

 ytd_sales ,

 notes ,

 pubdate

FROM

 @titleupdate

WHERE

 alreadyExists = 0

Select @insertRowCount = @@ROWCOUNT

select @numberRowsAffected = @insertRowCount + @updateRowCount

--select * from titles

SET NOCOUNT OFF

GO