Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE gdb_items_vw (name, definition) AS
SELECT 'A',
       '<GPCodedValueDomain2><CodedValues><CodedValue>
       <Code>Code A</Code><Name>Name A</Name>
       </CodedValue></CodedValues></GPCodedValueDomain2>'
FROM   DUAL UNION ALL
SELECT 'A',
       '<GPCodedValueDomain2><CodedValues><CodedValue>
       <Code>Code A</Code><Name>Name A</Name>'
FROM   DUAL;
2 rows affected

with function test_xmltable(v_xml clob) return number
is
  v_code VARCHAR2(255);
  v_desc VARCHAR2(255);
begin
  SELECT code, description
  INTO   v_code, v_desc
  FROM   xmltable(
           '/GPCodedValueDomain2/CodedValues/CodedValue' 
           passing xmltype(v_xml)
           columns
             code        varchar2(255) path './Code',
             description varchar2(255) path './Name'
         );
  return 1;
exception
  when others then
    return 0;
end;

select i.name as domain_name,
       test_xmltable(i.definition)
from   /*sde.*/gdb_items_vw i
where  test_xmltable(i.definition) = 0;


DOMAIN_NAME
TEST_XMLTABLE(I.DEFINITION)


A
0


with function test_xmltable(v_xml clob) return number
is
  v_code SYS.ODCIVARCHAR2LIST;
  v_desc SYS.ODCIVARCHAR2LIST;
begin
  SELECT code, description
  BULK COLLECT INTO v_code, v_desc
  FROM   xmltable(
           '/GPCodedValueDomain2/CodedValues/CodedValue' 
           passing xmltype(v_xml)
           columns
             code        varchar2(255) path './Code',
             description varchar2(255) path './Name'
         );
  return 1;
exception
  when others then
    return 0;
end;

select i.name as domain_name,
       test_xmltable(i.definition)
from   /*sde.*/gdb_items_vw i
where  test_xmltable(i.definition) = 0; 


DOMAIN_NAME
TEST_XMLTABLE(I.DEFINITION)


A
0