ALTER TABLE TBMFE
    ADD SAT_NUMERO_CANCELAMENTO INTEGER;

CREATE OR ALTER function fn_chave_xml (
    chave varchar(300),
    xml blob sub_type 0 segment size 80)
returns blob sub_type 0 segment size 80
as
declare variable vchave varchar(100);
declare variable p1 integer;
declare variable p2 integer;
declare variable attr varchar(100);
begin

  for
     select OSPLIT from split2(:CHAVE, '.')
  into
    :VCHAVE
  do begin
    
    :ATTR   = coalesce(fn_array(:VCHAVE,2,'|'),'');
    :VCHAVE = coalesce(fn_array(:VCHAVE,1,'|'),'');

    if(char_length(:ATTR) = 0)then
    begin
        P1 = position('<' ||:VCHAVE||'>' in XML) + char_length('<'||:VCHAVE||'>');
        P2 = position('</'||:VCHAVE||'>' in XML) - P1;

        XML = substring(XML from P1 for P2);
    end else
    begin
       P1 = position('<' ||:VCHAVE||''  in XML) + char_length('<'||:VCHAVE||'');
       P2 = position('</'||:VCHAVE||'>' in XML);-- - char_length('</'||:VCHAVE||'>');

       XML = substring(XML from P1 for P2);

       P1 = position('' ||:ATTR||'="' in XML) + char_length(''||:ATTR||'="') + 2;
       XML = substring(XML from P1 for char_length(XML));
       P2 = position('"' in XML) - 1;

       XML = substring(XML from 1 for P2);
    end

  end

  return XML;
end;

COMMENT ON PARAMETER FN_CHAVE_XML.CHAVE IS
'Para Chaves encadeas no xml use "."
EXP: CFeCanc.infCFe.dEmi

Para propriedades de uma chave use "|"
EXP: CFeCanc.infCFe|id';

ALTER TABLE TBMFE
    ADD CHAVE_CANCELAMENTO CHAR(44);

CREATE INDEX ITBMFE_PARCELAMENTO_05
ON TBMFE_PARCELAMENTO (FORMA_PAGAMENTO);

CREATE INDEX ITBMFE_PARCELAMENTO_04
ON TBMFE_PARCELAMENTO (PARCELAMENTO_ID);

