UFN_REGISTRATIONPACKAGE_GETPKGPRICES_TOITEMLISTXML

create function dbo.UFN_REGISTRATIONPACKAGE_GETPKGPRICES_TOITEMLISTXML
(
    @EVENTIDS UDT_GENERICID readonly
)
returns XML
with execute as caller
as
begin
return(
    select
        REGISTRATIONPACKAGE.ID,
        REGISTRATIONPACKAGE.NAME,
        EVENTPRICE.ID [EVENTPRICEID],
        EVENTPRICE.EVENTID [EVENTPRICEEVENTID],
        EVENT.NAME [EVENTNAME],
        EVENTPRICE.EVENTREGISTRATIONTYPEID,
        EVENTREGISTRATIONTYPE.DESCRIPTION [EVENTREGISTRATIONTYPEDESCRIPTION],
        EVENTPRICE.AMOUNT,
        EVENTPRICE.RECEIPTAMOUNT,
        EVENTPRICE.COST,
        EVENTPRICE.REGISTRATIONCOUNT
    from
        dbo.REGISTRATIONPACKAGE
        left join dbo.REGISTRATIONPACKAGEPRICE on REGISTRATIONPACKAGE.ID = REGISTRATIONPACKAGEPRICE.REGISTRATIONPACKAGEID
        left join dbo.EVENTPRICE on REGISTRATIONPACKAGEPRICE.EVENTPRICEID = EVENTPRICE.ID
        left join dbo.EVENTREGISTRATIONTYPE on EVENTPRICE.EVENTREGISTRATIONTYPEID = EVENTREGISTRATIONTYPE.ID
        left join dbo.EVENT on EVENTPRICE.EVENTID = EVENT.ID
    where
        REGISTRATIONPACKAGE.EVENTID in (select ID from @EVENTIDS)
    order by NAME, EVENTNAME, EVENTREGISTRATIONTYPEDESCRIPTION
    for xml raw('ITEM'),type,elements,root('PACKAGESPRICES'),BINARY BASE64
)
end