UFN_PAGE_PAGEREFERENCES

CREATE function dbo.UFN_PAGE_PAGEREFERENCES
(
    @PAGEID 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 ContextLinks that navigate to this page

    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('tns:PageDefinitionSpec/tns:PageHeader/tns:ContextLinks/tns:ContextLink') as page(contextlinks)
        where page.contextlinks.value('@PageID', 'nvarchar(max)') like '%' + cast(@PAGEID as nvarchar(36)) + '%';

    -- find ShowPage actions that navigate to this page

    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:ShowPage') as page(showpageactions)
        where page.showpageactions.value('@PageID', 'uniqueidentifier') = @PAGEID;

    -- find PostAction events that navigate to this page

    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:PostActionEvent/common:GoToPage/common:SpecificPage') as page(postactions)
        where page.postactions.value('@PageID', 'uniqueidentifier') = @PAGEID;

    -- now build the results

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

    return;

end;