UFN_REPORT_PAGEREFERENCES

CREATE function dbo.UFN_REPORT_PAGEREFERENCES
(
    @REPORTID uniqueidentifier
)
returns @TABLE table
(
    PAGEID uniqueidentifier
)
with execute as caller
as
begin

    -- build a temp table containing the page ID

    declare @t table (ID uniqueidentifier);

    -- find pages with actions that use this report

    with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
    insert into @t
        select distinct P.ID
        from dbo.PAGEDEFINITIONCATALOG as P
            cross apply P.PAGEDEFINITIONSPEC.nodes('//common:ShowReport') as page(reportactions)
        where page.reportactions.value('@ReportID', 'uniqueidentifier') = @REPORTID;

    -- find sections that use this data list

    with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
    insert into @t
        select P.ID
        from dbo.PAGEDEFINITIONCATALOG as P
            cross apply P.PAGEDEFINITIONSPEC.nodes('//tns:Report') as page(reportsections)
        where page.reportsections.value('@ID', 'uniqueidentifier') = @REPORTID;

    -- now build the results

    insert into @TABLE
        select distinct T.ID
        from @t as T

    return;

end;