UFN_DATALIST_PAGEREFERENCES

CREATE function dbo.UFN_DATALIST_PAGEREFERENCES
(
    @DATALISTID 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 that use this data list as the PageNavigationTree

    with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
    insert into @t
        select P.ID
        from dbo.PAGEDEFINITIONCATALOG as P
        where P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageNavigationTree/@DataListID)[1]', 'nvarchar(max)') like '%' + cast(@DATALISTID as nvarchar(36)) + '%';

    -- find sections that use this data list

    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:DataList') as page(datalists)
        where 
            page.datalists.value('@ID', 'uniqueidentifier') = @DATALISTID

    -- now build the results

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

    return;

end;