UFN_DATAFORM_ISINSTANCEFORMFIELDIDVALID

CREATE function dbo.UFN_DATAFORM_ISINSTANCEFORMFIELDIDVALID
(
  @DATAFORMINSTANCEID uniqueidentifier,
  @FIELDID nvarchar(200)
)
returns bit
with execute as caller
as begin

    declare @FORMUIXML as xml;
    select @FORMUIXML = FORMUIXML
        from DATAFORMINSTANCECATALOG where ID = @DATAFORMINSTANCEID;

    declare @SEPINDEX as int;
    set @SEPINDEX = charindex('|', @FIELDID);

    declare @RET as bit;
    set @RET = 0;

    if @SEPINDEX > 0
    begin
      declare @CURRENTFIELD nvarchar(200);
      declare @CURRENTFIELDXML xml;

      while @SEPINDEX > 0
      begin
        set @CURRENTFIELD = substring(@FIELDID, 0, @SEPINDEX);

        if @CURRENTFIELDXML is null
        begin
          with xmlnamespaces ('bb_appfx_commontypes' as [c])
          select @CURRENTFIELDXML = T.c.query('.')
          from @FORMUIXML.nodes('c:FormMetaData/c:FormFields/c:FormField[@FieldID=sql:variable("@CURRENTFIELD")]') T(c);
        end
        else
        begin
          with xmlnamespaces ('bb_appfx_commontypes' as [c])
          select @CURRENTFIELDXML = T.c.query('.')
          from @CURRENTFIELDXML.nodes('c:FormField/c:Collection/c:Fields/c:FormField[@FieldID=sql:variable("@CURRENTFIELD")]') T(c);
        end

        if @CURRENTFIELDXML is null
          set @SEPINDEX = -1;
        else
        begin
          set @FIELDID = substring(@FIELDID, @SEPINDEX + 1, LEN(@FIELDID) - @SEPINDEX + 1);
          set @SEPINDEX = charindex('|', @FIELDID);
        end
      end

      if @CURRENTFIELDXML is not null
      begin
        with xmlnamespaces ('bb_appfx_commontypes' as [c])
        select @RET = @CURRENTFIELDXML.exist('c:FormField/c:Collection/c:Fields/c:FormField[@FieldID=sql:variable("@FIELDID")]');
      end
    end
    else
    begin

      with xmlnamespaces ('bb_appfx_commontypes' as [c])
      select @RET = @FORMUIXML.exist('c:FormMetaData/c:FormFields/c:FormField[@FieldID=sql:variable("@FIELDID")]');

    end

    return @RET;

end