Here is a sample, using the input as a text object.
--first , create the stored procedure.
if exists (select * from sysobjects
where id = object_id('dbo.usp_insert_pubs_authors') and sysstat & 0xf = 4)
drop procedure dbo.usp_insert_pubs_authors
GO
CREATE PROCEDURE usp_insert_pubs_authors (
@xml_doc TEXT )
AS
SET NOCOUNT ON
DECLARE @hdoc INT -- handle to XML doc
--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 @storesinsert TABLE (
stor_id varchar(16) ,
stor_name varchar(16) ,
stor_address varchar(16) ,
city varchar(16) ,
state varchar(16) ,
zip varchar(16)
)
INSERT @storesinsert
SELECT
stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip
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"
OPENXML (@hdoc, '/StoresDS/store', 2) WITH (
stor_id varchar(16) ,
stor_name varchar(16) ,
stor_address varchar(16) ,
city varchar(16) ,
state varchar(16) ,
zip varchar(16)
)
-- temp select just to show all the data in the @variabletable
select * from @storesinsert
BEGIN TRANSACTION
--insert into the stores table ( a "real" table) .. (from) the @variabletable (which now contains all the dataset data)
Insert into stores
(
stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip
)
SELECT
stor_id ,
stor_name ,
stor_address ,
city ,
state ,
zip
FROM @storesinsert
IF @@ERROR <> 0
BEGIN
RAISERROR('error updating database', 16, 1)
ROLLBACK Transaction
END
COMMIT TRAN
GO
--
--Now run the code using an example
--
--Here is the call. The input for the t-sql proc is
--text, but tsql doesn't allow localized text parameters
--so varchar is used here (for @xml_doc_temp).
--The xml string (below) follows what would be a typed dataset xml
--While here, it is manually created, the procedure would
--probably be called using the myTypeDataset.getXML() as the input parameter
--Naturally,the xml can contain 0, 1 or N number of "stores" for
--insertion (2 stores are used here)
declare @xml_doc_temp varchar(8000)
select @xml_doc_temp =
'
<StoresDS>
<store>
<stor_id>' + LEFT(NEWID(), 4) + '</stor_id>
<stor_name>' + LEFT(NEWID(), 16) + '</stor_name>
<stor_address>456 Hickory</stor_address>
<city>Charlotte</city>
<state>NC</state>
<zip>44444</zip>
</store>
<store>
<stor_id>' + LEFT(NEWID(), 4) + '</stor_id>
<stor_name>' + LEFT(NEWID(), 16) + '</stor_name>
<stor_address>456 Main</stor_address>
<city>Charlotte</city>
<state>NC</state>
<zip>33333</zip>
</store>
</StoresDS>
'
print @xml_doc_temp
EXEC usp_insert_pubs_authors @xml_doc_temp
GO
select * from stores order by stor_name
Maybe that will get you started.