UFN_REVENUETRANSACTION_TOP3DONORCHALLENGECLAIMS_TOITEMLISTXML

CREATE function dbo.UFN_REVENUETRANSACTION_TOP3DONORCHALLENGECLAIMS_TOITEMLISTXML
(
  @REVENUESPLITID uniqueidentifier
) 
returns xml
as begin

  declare @MATCHINGCLAIMS xml;

  set @MATCHINGCLAIMS = (
    select top 3 
      DONORCHALLENGEENCUMBERED.MATCHEDREVENUEID as DONORCHALLENGEREVENUEID,
      REVENUE.BASEAMOUNT as DONORCHALLENGEAMOUNT,
      REVENUE.TRANSACTIONCURRENCYID as DONORCHALLENGETRANSACTIONCURRENCYID,
      REVENUE.TYPE as DONORCHALLENGETYPE,
      (select top 1 CONSTITUENT.NAME from dbo.CONSTITUENT where CONSTITUENT.ID = REVENUE.CONSTITUENTID) as DONORCHALLENGECONSTITUENT
    from 
      dbo.DONORCHALLENGEENCUMBERED
    join dbo.FINANCIALTRANSACTION REVENUE 
      on DONORCHALLENGEENCUMBERED.MATCHEDREVENUEID = REVENUE.ID
    inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
    where 
      DONORCHALLENGEENCUMBERED.REVENUESPLITID = @REVENUESPLITID and
      DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 1 and
      REVENUE.DELETEDON is null
    order by cast(REVENUE.DATE as datetime) desc

    for xml raw('ITEM'), type, elements, root('DONORCHALLENGECLAIMS'), binary base64  
  );

  return @MATCHINGCLAIMS;
end