USP_DATALIST_REPORTDATARETRIEVALOBJECTS

create procedure dbo.USP_DATALIST_REPORTDATARETRIEVALOBJECTS
(
    @REPORTID uniqueidentifier
)
as
    set nocount on;

    with xmlnamespaces ('bb_appfx_report' as tns, 'bb_appfx_commontypes' as common)
    select 
        report.dataretrievalobjects.value('@ObjectName', 'nvarchar(max)') as OBJECTNAME,
        case report.dataretrievalobjects.value('@ObjectType', 'nvarchar(21)')
            when 'SQLView' then 'View'
            when 'SQLStoredProc' then 'Stored procedure'
            when 'SQLFunction' then 'Function'
            when 'SQLTableValueFunction' then 'Table-value function'
            when 'SQLTable' then 'Table'
        end as OBJECTTYPE

    from dbo.REPORTCATALOG as R
      cross apply R.REPORTSPECXML.nodes('tns:ReportSpec/tns:DataRetrieval/tns:CreateSQL') as report(dataretrievalobjects)

    where R.ID = @REPORTID