USP_PLEDGE_UPDATEINSTALLMENT

            CREATE procedure dbo.USP_PLEDGE_UPDATEINSTALLMENT
            (
                @PLEDGEID uniqueidentifier,
                @INSTALLMENTS xml,
                @CHANGEAGENTID uniqueidentifier,
                @CURRENTDATE datetime
            )
            as 
            set nocount on;

            declare @INSTALLMENTSPLITS xml;

            if @CHANGEAGENTID is null  
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

            if @CURRENTDATE is null  
                set @CURRENTDATE = GetDate();

            --We need to make sure all of the installments have an ID so the child objects will update properly                                         

            declare @TempTbl table (
               [ID] uniqueidentifier,
               [DATE] datetime,
               [AMOUNT] money,
               [BALANCE] money,
               [APPLIED] money,
               [SEQUENCE] int,
               [SPLITID] uniqueidentifier,
               [SPLITDESIGNATIONID] uniqueidentifier,
               [SPLITAMOUNT] money,
               [REVENUESPLITID] uniqueidentifier);

                --case when T2.split.value('(ID)[1]','nvarchar(50)') = '' then null else T2.split.value('(ID)[1]','nvarchar(50)') end AS 'SPLITID',

            insert into @TempTbl
            SELECT
                T1.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                T1.c.value('(DATE)[1]','datetime') AS 'DATE',
                T1.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
                T1.c.value('(BALANCE)[1]','money') AS 'BALANCE',
                T1.c.value('(APPLIED)[1]','money') AS 'APPLIED',
                T1.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
                T2.split.value('(ID)[1]','nvarchar(50)') AS 'SPLITID',
                T2.split.value('(DESIGNATIONID)[1]','uniqueidentifier') AS 'SPLITDESIGNATIONID',
                T2.split.value('(AMOUNT)[1]','money') AS 'SPLITAMOUNT',
                T2.split.value('(REVENUESPLITID)[1]', 'uniqueidentifier') as 'REVENUESPLITID'
            from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T1(c)
            cross apply T1.c.nodes('./INSTALLMENTSPLITS/ITEM') as T2(split);

            declare @TempSeqTbl table (
               [ID] uniqueidentifier,
               [SEQUENCE] int);

            insert into @TempSeqTbl
            select newid() ID, MISSINGID.SEQUENCE
                from (select distinct SEQUENCE from @TempTbl where id is null) MISSINGID

            update a
            set ID = b.ID
            from @TempTbl a
            inner join @TempSeqTbl b
                on a.SEQUENCE = b.SEQUENCE;

            set @INSTALLMENTS = (select distinct
                    ID, DATE, AMOUNT, BALANCE, APPLIED, SEQUENCE
                from @TempTbl
                for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64);

            set @INSTALLMENTSPLITS = (select distinct
                    SPLITID ID, ID INSTALLMENTID, SPLITDESIGNATIONID DESIGNATIONID, SPLITAMOUNT AMOUNT, REVENUESPLITID
                from @TempTbl
                for xml raw('ITEM'),type,elements,root('INSTALLMENTSPLITS'),BINARY BASE64);

            exec dbo.USP_INSTALLMENT_GETINSTALLMENTS_UPDATEFROMXML @PLEDGEID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE;

            exec dbo.USP_PLEDGE_GETINSTALLMENTSPLITS_UPDATEFROMXML @PLEDGEID, @INSTALLMENTSPLITS, @CHANGEAGENTID, @CURRENTDATE;