UFN_REVENUEBATCH_GETMEMBERSHIPS_TOITEMLISTXML

      create function [dbo].[UFN_REVENUEBATCH_GETMEMBERSHIPS_TOITEMLISTXML]
      (
        @MEMBERSHIPID uniqueidentifier
      )
      returns xml
      as
      begin
        return
        (
            select MEMBERSHIPPROGRAMID,
                    MEMBERSHIPLEVELID,
                    MEMBERSHIPLEVELTERMID,
                    MEMBERSHIPLEVELTYPECODEID,
                    NUMBEROFCHILDREN,
                    COMMENTS,
                    ISGIFT,
                    SENDRENEWALCODE,
                    EXPIRATIONDATE,
                    GIVENBYID,
                    MLT.AMOUNT,
                    (select ID, 
                            CONSTITUENTID, 
                            ISPRIMARY,
                            (select top 1 ID, 
                                    NAMEONCARD
                                from dbo.MEMBERSHIPCARD
                                where MEMBERSHIPCARD.MEMBERID = MEMBER.ID
                                order by MEMBERSHIPCARD.EXPIRATIONDATE desc
                                for xml raw('ITEM'), type, elements, root('MEMBERSHIPCARDS'), binary base64)
                         from dbo.MEMBER 
                         where MEMBER.MEMBERSHIPID = M.ID and ISDROPPED = 0
                         for xml raw('ITEM'), type, elements, root('MEMBERS'), binary base64)
                 from dbo.MEMBERSHIP M
                 inner join dbo.MEMBERSHIPLEVELTERM MLT on M.MEMBERSHIPLEVELTERMID = MLT.ID
                 where M.ID = @MEMBERSHIPID
                 for xml raw('ITEM'), type, elements, binary base64

        )
      end