Create   PROCEDURE [dbo].[GetXMLAsInput]

@xmlString varchar(max)

AS

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