stored procedure for insert into Table from XML Parameter in SQL Server.

--–@XmlData='<Content>

--–<Name>Suneel</Name>

--–<Email>sk@gmail.com</Email>

--–<Mobile>99889898989</Mobile>

--–<Item Course="HSC" Year="2004″ Mark="68 %"></Item>

--–<Item Course="SSC" Year="2006″ Mark="70 %"></Item>

--–<Item Course="B.Sc" Year="2009″ Mark="72 %"></Item>

--–<Item Course="MCA" Year="2012″ Mark="75 %"></Item>

--–<RegNo>70123</RegNo>

--–</Content>'

CREATE PROCEDURE TestProcToInsertUsingXml

(

@XmlData ntext=null

)

AS

BEGIN

SET NOCOUNT ON;

declare @TempXML xml

set @TempXML=CONVERT(xml,@XmlData)

declare @hDoc int

EXEC sp_xml_preparedocument  @hDoc output,@TempXML

Declare @Email varchar(50),@RegNo varchar(10)

select @RegNo=Q.RegNo

from OPENXML (@hDoc,N'/Content')

WITH(

RegNo varchar(10) 'RegNo'

)Q

IF NOT EXISTS(SELECT  RegNo FROM tblStudent where RegNo = @RegNo)

BEGIN

--— insert Into Registration Table

insert into tblStudent(StudentName,EmailId,MobileNo,RegId)

select Name,Email,Mobile,RegNo

from OPENXML (@hDoc,N'/Content')

WITH(

Name   varchar(10) 'Name',

Email  varchar(10) 'Email',

Mobile varchar(10) 'Mobile',

RegNo  varchar(10) 'RegNo'

)Std

set @RegNo=SCOPE_IDENTITY();

--— Insert Into Qualification Table

insert into tblStudentQfy(RegNo,Course,PassingYear,MarkObtained)

select @RegNo,Course,PYear,Mark

from OPENXML (@hDoc,N'/Content/Item')

WITH(

Course varchar(10) '@Course',

PYear  varchar(10) '@Year',

Mark   varchar(10) '@Mark'

)Qfy

END

else

BEGIN

Update  tblStudent

SET Name =s.Name,

Email=s.Email,

Mobile=s.Mobile

from OPENXML (@hDoc,N'/Content')

WITH(

Name    varchar(50) 'Name',

Email   varchar(10) 'Email',

Mobile  varchar(10) 'Mobile',

RegNo   varchar(10) 'RegNo'

) s

where @RegNo=RegNo

END

END