How to Insert XML Values in Database table?

Step1:

ALTER PROCEDURE CASH_SPM_BOOKCLOSE 
( 
@BookClose XMl, 
@Errormessage varchar(max) output 
) 
AS
BEGIN 
Declare @RootNode varchar(100)='BookClose', 
@BookClosingId bigint=0, 
@CollectionType varchar (50)='BookClose', 
@FromDate varchar(11),@ToDate varchar(11),
@MainProcess varchar(100)='BookClosing',
@OpeningDate datetime,
@Count int=0,@Val int=0;

DECLARE @XmlHandle int,@Sp1 int
 
 EXEC sp_xml_preparedocument @XmlHandle output,@BookClose 
 BEGIN TRANSACTION 
       
       SELECT @FromDate=OpeningDate,@ToDate=ClosingDate
            FROM OPENXML(@XmlHandle, @RootNode,2) 
                 WITH (OpeningDate datetime 'OpeningDate',ClosingDate datetime 'ClosingDate'); 
                 
         select @Count=Count(BOOK_CLOSING_ID) from CASH_BOOK_CLOSE
        
         EXEC CASH_SPU_CASH_BOOK_CLOSE @RootNode, @XmlHandle, @BookClosingId,@Count;
           
     SET @Sp1=@@ERROR                                      
     IF (@@ERROR<> 0 OR @Sp1<>0 )
         BEGIN 
             ROLLBACK TRAN 
             PRINT 'TRANSACTION FAILED' 
         END   
     ELSE 
         BEGIN 
             COMMIT TRAN 
             PRINT 'TRANSACTION SUCCESSED' 
         END  
          
     EXEC sp_xml_removedocument @XmlHandle          
END


------------------------------------------------------------------------------

ALTER PROCEDURE CASH_SPU_CASH_BOOK_CLOSE
(
    @RootNode varchar (100),
    @XmlHandle int,
    @BookClosingId bigint,
    @Flag int
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @TimeStamp datetime =getdate(),
            @ColumnId int=0,
            @FromDate datetime,
            @ToDate datetime,
            @PaidAmount decimal(15,2),
            @ReceiveAmount decimal(15,2),
            @OpeningAmount decimal(15,2);
   
    SET @PaidAmount = 0.00
    SET    @ReceiveAmount = 0.00
    SET    @OpeningAmount = 0.00

    SELECT  @FromDate=OpeningDate,
            @ToDate=ClosingDate
    FROM OPENXML(@XmlHandle, @RootNode,2) 
    WITH (OpeningDate datetime 'OpeningDate',ClosingDate datetime 'ClosingDate');

        
    IF @FromDate=@ToDate 
    BEGIN
        SELECT @BookClosingId=ISNULL(BOOK_CLOSING_ID,0)
        FROM CASH_BOOK_CLOSE where CLOSING_DATE is null AND COLUMN_ID=1
   
        SELECT @OpeningAmount=ISNULL(OPENING_BALANCE,0.00),
               @ReceiveAmount=ISNULL(RECEIVE_AMOUNT,0.00),
               @PaidAmount=ISNULL(PAYMENT_AMOUNT,0.00)
        FROM CASH_REVENUE_DETAILS where COLUMN_ID=1
          
        DECLARE @ClosingAmount decimal(15,2) =(@OpeningAmount+@ReceiveAmount)-@PaidAmount

        UPDATE [CASH_BOOK_CLOSE]
               SET OFFICE_ID=XmlData.OfficeId,
                   RECEIVED_AMOUNT=@ReceiveAmount,
                   PAID_AMOUNT=@PaidAmount,
                   OPENING_DATE=XmlData.OpeningDate,
                   CLOSING_DATE=XmlData.ClosingDate,
                   IS_CASH_BOOK_CLOSED=XmlData.IsCashBookClosed,
                   COLUMN_ID=1,
                   USER_ID=XmlData.UserId,
                   MAC_ID=XmlData.MacId,
                   ROLE_ID=XmlData.RoleId,
                   TIME_STAMP=@TimeStamp,
                   TRACKER_ID=XmlData.TrackerId,
                   OPENING_BALANCE=@OpeningAmount,
                   CLOSING_BALANCE=@ClosingAmount
               FROM OPENXML(@XmlHandle, @RootNode,2)
               WITH(OfficeId int 'OfficeId',
                    --ReceivedAmount Numeric(15,2) 'ReceivedAmount',
                    --PaidAmount Numeric(15,2) 'PaidAmount',
                    OpeningDate datetime 'OpeningDate',
                    ClosingDate datetime 'ClosingDate',
                    IsCashBookClosed bit 'IsCashBookClosed',               
                    --ColumnId int 'ColumnId',
                    UserId int 'UserId',
                    MacId varchar(30) 'MacId',
                    RoleId int 'RoleId',
                    TrackerId varchar(100) 'TrackerId'
                   )XmlData
        WHERE BOOK_CLOSING_ID=@BookClosingId AND COLUMN_ID=1
       
   
        UPDATE CASH_REVENUE_DETAILS
               SET OPENING_BALANCE=@ClosingAmount,
                   RECEIVE_AMOUNT=ISNULL(RECEIVE_AMOUNT,0.00)-@ReceiveAmount,
                   PAYMENT_AMOUNT=ISNULL(PAYMENT_AMOUNT,0.00)-@PaidAmount
                WHERE COLUMN_ID=1   

     END
END
----------------------------------------------------------------------------       


Step2:

declare @p1 xml
set @p1=convert(xml,N'<BookClose><RoleId>9</RoleId><ActivityId>0</ActivityId><ActivityCode>BA88</ActivityCode><ExecutionMode>Create</ExecutionMode><UserId>40</UserId><TrackerId>i5cqm41vqa1mqdaidug0i1kh</TrackerId><MacId>127.0.0.1</MacId><DiscomCode>AVVNL</DiscomCode><BookClosingId>0</BookClosingId><BookOwner>0</BookOwner><ClosingDate>11-Feb-2013</ClosingDate><ColumnId>1</ColumnId><CounterId>0</CounterId><FinYrId>0</FinYrId><IsCashBookClosed>0</IsCashBookClosed><JEOfficeId>0</JEOfficeId><OfficeId>1602210</OfficeId><OpeningDate>15-Jan-2013</OpeningDate><PaidAmount>10650528.00</PaidAmount><ReceivedAmount>10602993.00</ReceivedAmount></BookClose>')
EXEC CASH_SPM_BOOKCLOSE @BookClose=@p1,@Errormessage=N''

-----------------------------------------------------------------------------