UFN_SIMPLEDATALIST_DATAFORMREFERENCES

create function dbo.UFN_SIMPLEDATALIST_DATAFORMREFERENCES
(
    @SIMPLEDATALISTID uniqueidentifier
)
returns @TABLE table
(
    DATAFORMINSTANCEID uniqueidentifier
)
with execute as caller
as
begin

    -- find forms that use this simple data list

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @TABLE
        select distinct INSTANCE.ID
        from dbo.DATAFORMINSTANCECATALOG as INSTANCE
            inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
            cross apply TEMPLATE.TEMPLATESPECXML.nodes('//common:SimpleDataList') as dataform(simpledatalistfields)
        where 
            simpledatalistfields.value('@SimpleDataListID', 'uniqueidentifier') = @SIMPLEDATALISTID;

    return;

end;