USP_GETSPLITPROCESSORMAPPINGXML

     create procedure dbo.USP_GETSPLITPROCESSORMAPPINGXML
     (
        @MAPPINGRULES xml,
        @TRANSACTIONTYPEFILTERVALUE varchar(100),
        @ORIGINALTEMPLATENAME varchar(100),
        @NEWTEMPLATENAME varchar(100),
        @ORIGINALOWNERNAMEOPENINGTAG varchar(100),
        @ORIGINALOWNERNAMECLOSINGTAG varchar(100),
        @NEWOWNERNAMEOPENINGTAG varchar(100),
        @NEWOWNERNAMECLOSINGTAG varchar(100),
        @NEWMAPPINGFINALXML xml output
     )
    as
    begin
        declare @BATCHSEPARATIONFILTEREDXML xml
        declare @NEWMAPPINGRULE nvarchar(MAX)
        declare @COUNT int 
        declare @MAXCOUNT int
        declare @PARENTRULEID uniqueidentifier

        select @BATCHSEPARATIONFILTEREDXML = @MAPPINGRULES.query('/MAPPINGRULES/ITEM[.//*[local-name() = sql:variable("@TRANSACTIONTYPEFILTERVALUE")]/text()="True"]');
        set @NEWMAPPINGRULE = N'<?xml version="1.0" encoding="utf-16"?><MAPPINGRULES>'  + convert(nvarchar(max), @BATCHSEPARATIONFILTEREDXML) + '</MAPPINGRULES>' 
        set @NEWMAPPINGFINALXML = convert(xml, @NEWMAPPINGRULE)
        select @MAXCOUNT = count(1) from @NEWMAPPINGFINALXML.nodes('/MAPPINGRULES/ITEM') T(c)
        set @COUNT = 1

        while @COUNT <= @MAXCOUNT
            begin
                set @PARENTRULEID = @NEWMAPPINGFINALXML.value('(/MAPPINGRULES/ITEM/ID)[sql:variable("@Count")][1]', 'uniqueidentifier')

                --Replace  template value

                set @NEWMAPPINGFINALXML.modify('replace value of (/MAPPINGRULES/ITEM//*[local-name() = sql:variable("@ORIGINALTEMPLATENAME")]/text())[sql:variable("@Count")][1]
                with (/MAPPINGRULES/ITEM//*[local-name() = sql:variable("@NEWTEMPLATENAME")]/text())[sql:variable("@Count")][1]')

                --Create new node parent rule id and add parent rule id i.e id to that

                declare @PARENTRULEIDXMLTAG xml =  '<PARENTRULEID>' + convert(nvarchar(50), @PARENTRULEID)  +'</PARENTRULEID>'
                set @NEWMAPPINGFINALXML.modify('insert sql:variable("@PARENTRULEIDXMLTAG") as first into (/MAPPINGRULES/ITEM)[sql:variable("@Count")][1]')

                --Replace id column value with new child ruleid

                declare @RULEID uniqueidentifier  
                set @RULEID = newid()  
                set @NEWMAPPINGFINALXML.modify('replace value of (/MAPPINGRULES/ITEM/ID/text())[sql:variable("@Count")][1]
                                            with sql:variable("@RULEID")[1]')
                set @COUNT=@COUNT + 1
            end

            --Delete OWNERIDS tag

            declare @ORIGINALOWNERNAME varchar(100)

            set  @ORIGINALOWNERNAME = substring(@ORIGINALOWNERNAMEOPENINGTAG, 2, len(@ORIGINALOWNERNAMEOPENINGTAG) - 2 )
            set  @NEWMAPPINGFINALXML.modify('delete /MAPPINGRULES/ITEM//*[local-name() = sql:variable("@ORIGINALOWNERNAME")]')

            set @NEWMAPPINGFINALXML = replace(replace(cast(@NEWMAPPINGFINALXML as nvarchar(max)), 
                                @NEWOWNERNAMEOPENINGTAG, 
                                @ORIGINALOWNERNAMEOPENINGTAG), 
                                @NEWOWNERNAMECLOSINGTAG, 
                                @ORIGINALOWNERNAMECLOSINGTAG)

            select @NEWMAPPINGFINALXML
    end