how to solve this problem ?

[CODE]
Create procedure GetXMLAsInput
@xmlString varchar(1000)
as
insert into person values(--what should i do to iterate over the xml and perform multiple insertion)
[/CODE]

Re: how to solve this problem ?

Refer the following link

http://weblogs.asp.net/manojkdotnet/archive/2010/01/26/bulk-insert-using-gridview-and-sql-server-xml.aspx

http://stackoverflow.com/questions/7146265/bulk-insert-using-xml

http://www.dotnetspider.com/resources/31343-Bulk-Insert-using-XML-Data-SQL-Server.aspx

Re: how to solve this problem ?

/*
	Hi,
	Below is a very simple scenario where you can parse the xml in sql server (2005 and above) and put data into a table.
	You can use the sql server xml data type for the purpose.
*/

Declare @xmlData Xml

Set @xmlData = '<data>
					<row Id="1">AAA</row>
					<row Id="2">BBB</row>
					<row Id="3">CCC</row>
				</data>'


-- In the table below we will put data from the xml.
Declare @Data Table (Id Int, Name Varchar(100))

--
Select r.c.value('@Id[1]', 'int'),
		r.c.value('.[1]', 'varchar(100)')
From @xmlData.nodes ('data/row') r(c)


-- Note your stored procedure should look like the one below

Create Procedure [Stored Proc Name]
	@xmlData Xml
Begin
	-- Code
End


-- For more stuff on xml data type please visit  http://msdn.microsoft.com/en-us/library/ms190798

Re: how to solve this problem ?

Create PROCEDURE [dbo].[GetXMLAsInput]

@xmlString varchar(max)

DECLARE @idoc int

DECLARE @ERR Int

SET @ERR = 0

BEGIN

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

BEGIN TRY

insert into person (personname,personmobileno,persondob)

SELECT personname,personmobileno,persondob

FROM OPENXML (@idoc, '/NewDataSet/Nodename',2)with
(personname varchar(100),personmobileno int ,persondob datetime)

COMMIT TRAN

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK

-- Raise an error with the details of the exception

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SELECT @ErrMsg = ERROR_MESSAGE(),

@ErrSeverity

= ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

EXEC sp_xml_removedocument @idoc

--SELECT @ERR

END