UFN_SALESORDER_GETMEMBERSHIPITEMS_TOXML

    CREATE function dbo.UFN_SALESORDER_GETMEMBERSHIPITEMS_TOXML
    (
    @SALESORDERITEMID uniqueidentifier
    )
            returns xml
            as begin
                return (select
                            MEMBERSHIPID,
                            MEMBERSHIPPROGRAMID,
                            MEMBERSHIPLEVELID,
                            MEMBERSHIPLEVELTERMID,
                            MEMBERSHIPLEVELTYPECODEID,
                            NUMBEROFCHILDREN,
                            COMMENTS,
                            ISGIFT,
                            SENDRENEWALCODE,
                            EXPIRATIONDATE,
                            GIVENBYID,

                            (select
                                SALESORDERITEMMEMBER.ID as SALESORDERITEMMEMBERID,
                                SALESORDERITEMMEMBER.MEMBERID,
                                SALESORDERITEMMEMBER.CONSTITUENTID,
                                SALESORDERITEMMEMBER.ISPRIMARY

                                from dbo.SALESORDERITEMMEMBER
                                where SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID 
                                for xml raw('ITEM'),type,elements,BINARY BASE64
                            ) as MEMBERS,

                            (select 
                                    SALESORDERITEMMEMBERSHIPCARD.SALESORDERITEMMEMBERID,
                                    SALESORDERITEMMEMBERSHIPCARD.MEMBERSHIPCARDID,
                                    SALESORDERITEMMEMBERSHIPCARD.NAMEONCARD,
                                    SALESORDERITEMMEMBERSHIPCARD.EXPIRATIONDATE

                                    from dbo.SALESORDERITEMMEMBERSHIPCARD
                                        inner join dbo.SALESORDERITEMMEMBER on SALESORDERITEMMEMBERSHIPCARD.SALESORDERITEMMEMBERID = SALESORDERITEMMEMBER.ID
                                    where SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID
                                    for xml raw('ITEM'),type,elements,BINARY BASE64
                            ) as MEMBERSHIPCARDS,

                            (select
                                SALESORDERITEMMEMBERSHIPADDON.ADDONID,
                                SALESORDERITEMMEMBERSHIPADDON.ADDONTYPECODE,
                                ADDONITEM.PRICE,
                                convert(int, ADDONITEM.QUANTITY) as QUANTITY,
                                ---Use NEWGUID view to get around the fact that you can't call newid() from a function

                                (select ID from dbo.NEWGUID) as SPLITID,
                                SALESORDERITEMMEMBERSHIPADDON.ID as SALESORDERITEMID

                                from dbo.SALESORDERITEMMEMBERSHIPADDON
                                inner join dbo.SALESORDERITEM ADDONITEM on SALESORDERITEMMEMBERSHIPADDON.ID = ADDONITEM.ID
                                where SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = SALESORDERITEMMEMBERSHIP.ID 
                                for xml raw('ITEM'),type,elements,BINARY BASE64
                            ) as ADDONS
                            from dbo.SALESORDERITEMMEMBERSHIP
                            where SALESORDERITEMMEMBERSHIP.ID = @SALESORDERITEMID
                            for xml raw('ITEM'),type,elements,root('MEMBERSHIPFIELDS'),BINARY BASE64
                        )
                end