--создаем таблицу
create table tmp_xml (xml XMLType);

--заполняем таблицу
insert into tmp_xml (xml) values (XMLType(
'<RESPONSE>
  <DATA REQUEST_ID="111">
    <ENTITY name="PACKAGE_001">
      <ATTRIBUTE name="PERS_ID" value="1111" />
      <ATTRIBUTE name="FIRST_NAME" value="OLGA" />
      <ATTRIBUTE name="SURNAME" value="NOVIKOVA" />
    </ENTITY>
    <ENTITY name="PACKAGE_001">
      <ATTRIBUTE name="PERS_ID" value="2222" />
      <ATTRIBUTE name="FIRST_NAME" value="ANNA" />
      <ATTRIBUTE name="SURNAME" value="ANTONOVA" />
    </ENTITY>
    <ENTITY name="PACKAGE_002">
      <ATTRIBUTE name="PERS_ID" value="1111" />
      <ATTRIBUTE name="PASSP_NO" value="AAA012345678" />
      <ATTRIBUTE name="ISSUE_DATE" value="01.01.2000" />
    </ENTITY>
  </DATA>
</RESPONSE>'
));

--парсим даныне из таблицы
select p.id
     , max(p.person_surname) person_surname
     , max(p.passport_no) passport_no
     , max(p.passport_issue_date) passport_issue_date
from (select d.extract('//ENTITY//ATTRIBUTE[@name="PERS_ID"]/@value').getStringVal() id
           , d.extract('//ENTITY//ATTRIBUTE[@name="SURNAME"]/@value').getStringVal() person_surname
           , d.extract('//ENTITY//ATTRIBUTE[@name="PASSP_NO"]/@value').getStringVal() passport_no
           , d.extract('//ENTITY//ATTRIBUTE[@name="ISSUE_DATE"]/@value').getStringVal() passport_issue_date
      from tmp_xml t
         , table(XMLSequence(t.xml.extract('RESPONSE/DATA/ENTITY'))) d
     ) p
group by p.id;

declare 
  xsdclob clob := '<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://localhost" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="RESPONSE">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="DATA">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="ENTITY" maxOccurs="unbounded" minOccurs="0">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="ATTRIBUTE" maxOccurs="unbounded" minOccurs="0">
                      <xs:complexType>
                        <xs:simpleContent>
                          <xs:extension base="xs:string">
                            <xs:attribute type="xs:string" name="name" use="optional"/>
                            <xs:attribute type="xs:string" name="value" use="optional"/>
                          </xs:extension>
                        </xs:simpleContent>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                  <xs:attribute type="xs:string" name="name" use="optional"/>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            <xs:attribute type="xs:byte" name="REQUEST_ID"/>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>';
begin
  begin
    dbms_xmlschema.deleteschema('myxsd.xsd', 3); 
  exception
    when others then
      null;
  end;
  begin
    dbms_xmlschema.registerSchema('myxsd.xsd', xsdclob, local => true, genTypes => false, genbean => false, genTables => false);
  exception
    when others then
      null;
  end;
end;

create or replace procedure XML_FILE_VALIDATOR is

xml XMLType;

  xml_clob1 clob := '<?xml version="1.0" encoding="windows-1257" ?>
<RESPONSE xmlns="http://localhost"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://localhost myxsd1.xsd">
  <DATA REQUEST_ID="111">
    <ENTITY name="PACKAGE_001">
      <ATTRIBUTE name="PERS_ID" value="1111" />
      <ATTRIBUTE name="FIRST_NAME" value="OLGA" />
      <ATTRIBUTE name="SURNAME" value="NOVIKOVA" />
    </ENTITY>
    <ENTITY name="PACKAGE_001">
      <ATTRIBUTE name="PERS_ID" value="2222" />
      <ATTRIBUTE name="FIRST_NAME" value="ANNA" />
      <ATTRIBUTE name="SURNAME" value="ANTONOVA" />
    </ENTITY>
  </DATA>
</RESPONSE>';
begin
 xml := xmltype(xml_clob1);


    if xml.isschemavalid('myxsd1.xsd')  = 1 then
    dbms_output.put_line ('XML document is VALID');

  else
    dbms_output.put_line ('XML document is INVALID => '||sqlerrm);

  end if;
end;