Here's an example procedure that accepts XML as a string, and shreds it into tables. It's pretty gnarly to write this by hand, if you're doing many of these best to write some sort of generator based on your table definition.
The inputted xml is pretty simple, and attribute based
<xml>
<agent addr1='123 Main Street' city='Toronto' />
<agent addr1='123 Main Street' city='Toronto' />
</xml>
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_INSERT_agents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_INSERT_agents]
GO
CREATE PROCEDURE sp_INSERT_agents
@strXML ntext
AS
BEGIN
-- NOTE: This procedure was generated by WCG:ITX DB/XML mapping utility
-- Please do not update this code by hand.
DECLARE @RC int
DECLARE @iDoc int
DECLARE @dtcurrenttime datetime
SET @dtcurrenttime = CURRENT_TIMESTAMP
-- Field variables
DECLARE @addr1 varchar(50)
DECLARE @addr2 varchar(50)
DECLARE @agentid char(13)
DECLARE @city varchar(50)
DECLARE @email varchar(50)
DECLARE @fax varchar(25)
DECLARE @mobile varchar(25)
DECLARE @muli char(1)
DECLARE @name varchar(50)
DECLARE @notes varchar(500)
DECLARE @phone varchar(25)
DECLARE @state char(2)
DECLARE @zip varchar(10)
DECLARE @part char(1)
EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML
-- Create a temporary return table
create table #return
( err varchar(50), agentid char(13))
-- Set NOCOUNT ON, to allow data to be returned from the temporary table.
SET NOCOUNT ON
DECLARE @ElementCursor CURSOR
SET @ElementCursor = CURSOR SCROLL DYNAMIC FOR
SELECT addr1, addr2, agentid, city, email, fax, mobile, muli, name, notes, phone, state, zip, part FROM OPENXML( @iDoc, "//agent", 2 )
WITH( addr1 varchar(50) '@addr1', addr2 varchar(50) '@addr2', agentid char(13) '@dt', city varchar(50) '@city', email varchar(50) '@email', fax varchar(25) '@fax', mobile varchar(25) '@mobile', muli char(1) '@muli', name varchar(50) '@name', notes varchar(500) '@notes', phone varchar(25) '@phone', state char(2) '@state', zip varchar(10) '@zip', part char(1) '@part')
OPEN @ElementCursor
FETCH NEXT FROM @ElementCursor INTO @addr1, @addr2, @agentid, @city, @email, @fax, @mobile, @muli, @name, @notes, @phone, @state, @zip, @part
BEGIN TRANSACTION
WHILE @@FETCH_STATUS = 0
BEGIN
-- Convert any temp values to real date time values
BEGIN
INSERT into dbo.agents
(addr1, addr2, agentid, city, email, fax, mobile, muli, name, notes, phone, state, zip, part, dtmodified)
values
(@addr1, @addr2, @agentid, @city, @email, @fax, @mobile, @muli, @name, @notes, @phone, @state, @zip, @part, @dtcurrenttime)
END
-- Check for any errors on the insert / update
IF @@error <> 0
BEGIN
INSERT into #return
(err)
values
(@@error)
END
FETCH NEXT FROM @ElementCursor INTO @addr1, @addr2, @agentid, @city, @email, @fax, @mobile, @muli, @name, @notes, @phone, @state, @zip, @part
END
COMMIT TRANSACTION
CLOSE @ElementCursor
DEALLOCATE @ElementCursor
EXECUTE sp_xml_removedocument @iDoc
-- Return the temporary data, containing any errors
SELECT * from #return
END
GO
