UFN_REVENUEBATCH_GETMEMBERSHIP_TOITEMLISTXML

            create function dbo.UFN_REVENUEBATCH_GETMEMBERSHIP_TOITEMLISTXML
            (
                @MEMBERSHIPID uniqueidentifier
            )
            returns xml
            as
                begin
                    return
                    (
                        select MEMBERSHIPPROGRAMID,
                                MEMBERSHIPLEVELID,
                                MEMBERSHIPLEVELTERMID,
                                MEMBERSHIPLEVELTYPECODEID,
                                NUMBEROFCHILDREN,
                                COMMENTS,
                                ISGIFT,
                                SENDRENEWALCODE,
                                GIVENBYID,
                                MLT.AMOUNT as AMOUNT,
                                (select ID, 
                                        CONSTITUENTID, 
                                        ISPRIMARY,
                                        (select top 1 ID, 
                                                NAMEONCARD,
                                                EXPIRATIONDATE
                                            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,root('MEMBERSHIPS'),BINARY BASE64
                    )
                end