USP_REVENUE_GETSPLITS_MERGEDESIGNATIONSTOXML

CREATE procedure dbo.USP_REVENUE_GETSPLITS_MERGEDESIGNATIONSTOXML (
    @SPLITS xml,
    @OLDSPLITS xml,
    @MERGEDDESIGNATIONS xml = null output
    )
as
begin
    set @MERGEDDESIGNATIONS = (
            select ISNULL(OS.[DESIGNATIONID], S.[DESIGNATIONID]) as ID,
                ISNULL(OS.[TRANSACTIONAMOUNT], '') as OLDAMOUNT,
                ISNULL(S.[AMOUNT], '') as NEWAMOUNT,
                ISNULL(OS.[TRANSACTIONCURRENCYID], S.[TRANSACTIONCURRENCYID]) as TRANSACTIONCURRENCYID
            from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@OLDSPLITS) OS
            full outer join dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) S on OS.DESIGNATIONID = S.DESIGNATIONID
            for xml raw('ITEM'),
                type,
                elements,
                root('DESIGNATIONS'),
                binary BASE64
            )
end