ASP.NET - Multiple insert into sql table using xml

HI,

I am formatting xml string in a string builder like this:

<Root>
<ImpactID>1</ImpactID>
<ImpactData>abc</ImpactData>

<ImpactID>1</ImpactID>
<ImpactData>xyz</ImpactData>

<ImpactID>1</ImpactID>
<ImpactData>pqr</ImpactData>
</Root>

I want to pass this string to sql stored procedure, extract the data and insert into a table.

Can anybody help me in this regard.

Thanks.

I have updated the your input xml by adding data node name to repeating data,, its required

CREATE PROCEDURE insertdata
(@data xml)
as
begin
INSERT INTO tablename(col1,col2)
SELECT
ParamValues.ID.value('./ImpactID[1]','int') AS ImpactID,
ParamValues.ID.query('./ImpactData').value('.', 'varchar(max)') AS ImpactData
FROM @Ids.nodes('/Root/Data') as ParamValues(ID)


--FOR VALIDATING THE INPUT
--DECLARE @Ids xml
--SET @Ids ='<Root>
--             <Data>
--                 <ImpactID>2</ImpactID>
--                 <ImpactData>abc</ImpactData>
                 
--             </Data>
--             <Data>
--                 <ImpactID>3</ImpactID>
--                 <ImpactData>xyz</ImpactData>
                 
--             </Data>
--      </Root>'
--SELECT

--ParamValues.ID.value('./ImpactID[1]','int') AS ImpactID,
--ParamValues.ID.query('./ImpactData').value('.', 'varchar(max)') AS ImpactData
--FROM @Ids.nodes('/Root/Data') as ParamValues(ID)



Happy Coding :)

I have updated the your input xml by adding data node name to repeating data,, its required

CREATE PROCEDURE insertdata
(@data xml)
as
begin
INSERT INTO tablename(col1,col2)
SELECT
ParamValues.ID.value('./ImpactID[1]','int') AS ImpactID,
ParamValues.ID.query('./ImpactData').value('.', 'varchar(max)') AS ImpactData
FROM @Ids.nodes('/Root/Data') as ParamValues(ID)


--FOR VALIDATING THE INPUT
--DECLARE @Ids xml
--SET @Ids ='<Root>
--             <Data>
--                 <ImpactID>2</ImpactID>
--                 <ImpactData>abc</ImpactData>
                 
--             </Data>
--             <Data>
--                 <ImpactID>3</ImpactID>
--                 <ImpactData>xyz</ImpactData>
                 
--             </Data>
--      </Root>'
--SELECT

--ParamValues.ID.value('./ImpactID[1]','int') AS ImpactID,
--ParamValues.ID.query('./ImpactData').value('.', 'varchar(max)') AS ImpactData
--FROM @Ids.nodes('/Root/Data') as ParamValues(ID)



Happy Coding :)