USP_DATALIST_DATAFORMEXTENSIONS

CREATE procedure dbo.USP_DATALIST_DATAFORMEXTENSIONS
(
    @DATAFORMINSTANCEID uniqueidentifier
)
as
    set nocount on;

    select DFX.ID, 
           INSTANCE.FORMNAME, 
           INSTANCE.DESCRIPTION,
           case when TEMPLATE.MODE = 0 then TEMPLATE.TEMPLATESPECXML.value('declare namespace bbv="bb_appfx_viewdataformtemplate";
                                                                            /bbv:ViewDataFormTemplateSpec[1]/@Author', 'nvarchar(500)')
                when TEMPLATE.MODE = 1 then TEMPLATE.TEMPLATESPECXML.value('declare namespace bbe="bb_appfx_editdataformtemplate";
                                                                            /bbe:EditDataFormTemplateSpec[1]/@Author', 'nvarchar(500)')
                when TEMPLATE.MODE = 2 then TEMPLATE.TEMPLATESPECXML.value('declare namespace bba="bb_appfx_adddataformtemplate";
                                                                            /bba:AddDataFormTemplateSpec[1]/@Author', 'nvarchar(500)')
           end as AUTHOR,
           DFX.ENABLED,
           TEMPLATE.ID as TEMPLATEID,
           case when TEMPLATE.MODE = 0 then 19
                when TEMPLATE.MODE = 1 then 3
                when TEMPLATE.MODE = 2 then 0
           end as CATALOGITEMTYPE
    from dbo.DATAFORMEXTENSION as DFX 
        inner join dbo.DATAFORMINSTANCECATALOG as INSTANCE on DFX.ID = INSTANCE.ID
        inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
    where DATAFORMINSTANCEID = @DATAFORMINSTANCEID
    order by SEQUENCE