SET TERM ^ ;

CREATE OR ALTER function FN_CHAVE_XML (
    CHAVE varchar(300),
    XML blob sub_type 0 segment size 80,
    DEFVALUE varchar(50) = null)
returns varchar(300)
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;

        IF(P2 > 0 AND P1 >= 0)THEN
        BEGIN
            XML = substring(XML from P1 for P2);
        END ELSE
        BEGIN
           XML = DEFVALUE;
        END
    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
^

SET TERM ; ^



SET TERM ^ ;

CREATE OR ALTER function FN_CHAVE_XML_V2 (
    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;

        IF(P2 > 0 AND P1 >= 0)THEN
        BEGIN
            XML = substring(XML from P1 for P2);
        END ELSE
        BEGIN
           XML = NULL;
        END
    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
^

SET TERM ; ^

