select d.extract('//ENTITY//ATTRIBUTE[@name="SURNAME"]/@value').getStringVal() surname
from table(XMLSequence(XMLType(
'<?xml version="1.0" encoding="windows-1257" ?>
<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>
  </DATA>
</RESPONSE>'
      ).extract('RESPONSE/DATA/ENTITY'))) d;

select d.extract('//ENTITY//ATTRIBUTE[@name="SURNAME"]/@value').getStringVal() surname
from table(XMLSequence(XMLType(
'<?xml version="1.0" encoding="windows-1257" ?>
<RESPONSE xmlns="http://testserver"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://testserver response.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>'
      ).extract(
          'RESPONSE/DATA/ENTITY'
        , 'xmlns="http://testserver"'
      ))) d;

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;

select d.extract('//SYSTEM_ERRORS/ERROR/text()').getStringVal() error
from table(XMLSequence(XMLType(
'<?xml version="1.0" encoding="windows-1257" ?>
<RESPONSE>
  <DATA>
    <SYSTEM_ERRORS>
      <ERROR_CODE>BAD_PERSON_ID</ERROR_CODE>
      <ERROR>Incorrect person ID, please check input data</ERROR>
      <ERROR>ORA-01403: no data found</ERROR>
      <ERROR>ORA-20000: person_id is empty</ERROR>
    </SYSTEM_ERRORS>
  </DATA>
</RESPONSE>'
      ).extract('RESPONSE/DATA/SYSTEM_ERRORS'))) d;

create or replace type XML_TABLE as table of XMLType
/
select ...
from table(cast(XMLSequence(...) as XML_TABLE))

create table test_xml (xml XMLType);
insert into test_xml (xml) values (XMLType(
'<?xml version="1.0" encoding="windows-1257" ?>
<RESPONSE>
  <DATA>
    <SYSTEM_ERRORS>
      <ERROR_CODE>BAD_PERSON_ID</ERROR_CODE>
      <ERROR>Incorrect person ID, please check input data</ERROR>
      <ERROR>ORA-01403: no data found</ERROR>
      <ERROR>ORA-20000: person_id is empty</ERROR>
    </SYSTEM_ERRORS>
  </DATA>
</RESPONSE>'));

create table test_xsl (xsl XMLType);
insert into test_xsl (xsl) values (XMLType(
'<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="no"/>

<xsl:template match="/">
<xsl:for-each select="RESPONSE/DATA/SYSTEM_ERRORS/ERROR">
  <xsl:choose>
    <xsl:when test="position()=1"></xsl:when>
    <xsl:otherwise>;</xsl:otherwise>
  </xsl:choose>
  <xsl:value-of select="."/>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>'));
commit;

select XMLTransform (
          xml.xml
        , (select xsl from test_xsl)
       ).getStringVal() result
from test_xml xml;

declare
  xml     XMLType := XMLType(
'<?xml version="1.0" encoding="windows-1257" ?>
<RESPONSE xmlns="http://testserver"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://testserver response.xsd">
  <DATA/>
</RESPONSE>');
  parser  xmlparser.Parser;
  xmldoc  xmldom.DOMDocument;
  xslelem xmldom.DOMElement;
  nspace  varchar2( 50 );
begin
  parser := xmlparser.newParser;
  xmlparser.setValidationMode(parser, false);
  xmlparser.setPreserveWhiteSpace(parser, true);
  xmlparser.parseClob(parser, xml.getClobVal());
  xmldoc := xmlparser.getDocument(parser);
  xslelem := xmldom.getDocumentElement(xmldoc);
  nspace := xmldom.getNamespace(xslelem);
  dbms_output.enable( 20000 );
  dbms_output.put_line('Namespace: '||nspace);
end;
/

create table test_xml (xml XMLType);
insert into test_xml (xml) values (XMLType(
'<?xml version="1.0" encoding="windows-1257" ?>
<RESPONSE xmlns="http://testserver"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://testserver response.xsd">
  <DATA>
    <SYSTEM_ERRORS>
      <ERROR_CODE>BAD_PERSON_ID</ERROR_CODE>
      <ERROR>Incorrect person ID, please check input data</ERROR>
      <ERROR>ORA-01403: no data found</ERROR>
      <ERROR>ORA-20000: person_id is empty</ERROR>
    </SYSTEM_ERRORS>
  </DATA>
</RESPONSE>'));

create table test_xsl (xsl XMLType);
insert into test_xsl (xsl) values (XMLType(
'<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="no"/>

<xsl:template match="RESPONSE">
<xsl:for-each select="RESPONSE/DATA/SYSTEM_ERRORS/ERROR">
  <xsl:choose>
    <xsl:when test="position()=1"></xsl:when>
    <xsl:otherwise>;</xsl:otherwise>
  </xsl:choose>
  <xsl:value-of select="."/>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>'));
commit;

select XMLTransform (
          xml.xml
        , (select xsl from test_xsl)
       ).getStringVal() result
from test_xml xml;

select
  XMLType(
'<RESPONSE xmlns="http://testserver"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://testserver response.xsd">
</RESPONSE>'
  ).transform(
    XMLType(
'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/*">
    <xsl:value-of select="namespace-uri(.)"/>
  </xsl:template>
</xsl:stylesheet>'
    )
  ).getStringVal() namespace 
from dual;

select XMLElement(
           "RECORD1"
         , XMLElement("RECORD1_1", 'Text')
         , XMLElement("RECORD1_2", XMLAttributes('NAME1_2' as "name", 'VALUE1_2' as "value"))
         , XMLElement(
               "SUBRECORD"
             , XMLElement("SUB1", 'Text')
             , XMLElement("SUB2", 'Text')
           )
         , XMLElement("NOTES")
       ) xml
from dual;

declare
  type t_property is record (key varchar2( 64 ), value varchar2( 64 ));
  type tbl_property is table of t_property;
  vt_property tbl_property;
  v_sql varchar2( 32767 );
  xml XMLType;
  i integer;
  ts timestamp;
begin
  dbms_output.enable( 20000 );
  dbms_random.seed(sysdate);

  select rownum, trunc(dbms_random.value *  100 )
  bulk collect into vt_property
  from all_objects
    where rownum <=  300 ;
  -- 1
  ts := systimestamp; dbms_output.put_line('Start 1: '||ts);
  v_sql := '<REQUIEST>';
  i := vt_property.first;
  while vt_property.exists(i) loop
    v_sql := v_sql||'<PARAM name="'||vt_property(i).key||'" value="'||vt_property(i).value||'"/>';
    i := vt_property.next(i);
  end loop;
  v_sql := v_sql||'</REQUIEST>';
  xml := XMLType(v_sql);
  dbms_output.put_line('End   1: '||systimestamp||' : '||((systimestamp - ts) day to second));
  --  2
  ts := systimestamp; dbms_output.put_line('Start 2: '||ts);
  v_sql := 'select XMLElement("REQUEST"';
  i := vt_property.first;
  while vt_property.exists(i) loop
    v_sql := v_sql||',XMLElement("PARAM",XMLAttributes('''||vt_property(i).key||''' as "name", '''||vt_property(i).value||''' as "value"))';
    i := vt_property.next(i);
  end loop;
  v_sql := v_sql||') from dual';
  execute immediate v_sql into xml;
  dbms_output.put_line('End   2: '||systimestamp||' : '||((systimestamp - ts) day to second));
  -- 3
  ts := systimestamp; dbms_output.put_line('Start 3: '||ts);
  xml := XMLType('<REQUEST/>');
  i := vt_property.first;
  while vt_property.exists(i) loop
    select updateXml(
               XMLType('<REQUEST/>')
             , '/REQUEST'
             , XmlElement("REQUEST", XMLAgg(XmlElement("PARAM", XMLAttributes(''||vt_property(i).key||'' as "name", 'VALUE1_2' as "value"))))
           )
    into xml
    from dual;
    i := vt_property.next(i);
  end loop;
  dbms_output.put_line('End   3: '||systimestamp||' : '||((systimestamp - ts) day to second));
end;
/

Start 1: 09.11.05 20:44:21.708170
End   1: 09.11.05 20:44:21.714328000 +03:00 : +000000000 00:00:00.006420000
Start 2: 09.11.05 20:44:21.714749
End   2: 09.11.05 20:44:21.732228000 +03:00 : +000000000 00:00:00.017736000
Start 3: 09.11.05 20:44:21.732637
End   3: 09.11.05 20:44:22.536805000 +03:00 : +000000000 00:00:00.804434000

create or replace type t_property as object (key varchar2( 64 ), value varchar2( 64 ))
/
create or replace type tbl_property as table of t_property
/

declare
  vt_property tbl_property;
  v_sql varchar2( 32767 );
  xml XMLType;
  i integer;
  ts timestamp;
begin
  dbms_output.enable( 20000 );
  dbms_random.seed(sysdate);
  select cast(multiset (
           select rownum, trunc(dbms_random.value *  100 ) from all_objects where rownum <=  300 
         ) as tbl_property)
  into vt_property
  from dual;

  ts := systimestamp; dbms_output.put_line('Start 1: '||ts);
  v_sql := '<REQUIEST>';
  i := vt_property.first;
  while vt_property.exists(i) loop
    v_sql := v_sql||'<PARAM name="'||vt_property(i).key||'" value="'||vt_property(i).value||'"/>';
    i := vt_property.next(i);
  end loop;
  v_sql := v_sql||'</REQUIEST>';
  xml := XMLType(v_sql);
  dbms_output.put_line('End   1: '||systimestamp||' : '||((systimestamp - ts) day to second));
  --  2
  ts := systimestamp; dbms_output.put_line('Start 2: '||ts);
  v_sql := 'select XMLElement("REQUEST"';
  i := vt_property.first;
  while vt_property.exists(i) loop
    v_sql := v_sql||
      ',XMLElement("PARAM",XMLAttributes('''||vt_property(i).key||''' as "name", '''||vt_property(i).value||''' as "value"))';
    i := vt_property.next(i);
  end loop;
  v_sql := v_sql||') from dual';
  execute immediate v_sql into xml;
  dbms_output.put_line('End   2: '||systimestamp||' : '||((systimestamp - ts) day to second));
  -- 3
  ts := systimestamp; dbms_output.put_line('Start 3: '||ts);
  select updateXml(
               XMLType('<REQUEST/>')
             , '/REQUEST'
             , XmlElement(
                   "REQUEST"
                 , XMLAgg(XmlElement("PARAM", XMLAttributes(''||t.key||'' as "name", ''||t.key||'' as "value")))
               )
           )
  into xml
  from table(cast(vt_property as tbl_property)) t;
  dbms_output.put_line('End   3: '||systimestamp||' : '||((systimestamp - ts) day to second));
end;
/

Start 1: 10.11.05 13:25:47.306544
End   1: 10.11.05 13:25:47.312837000 +03:00 : +000000000 00:00:00.006548000
Start 2: 10.11.05 13:25:47.313239
End   2: 10.11.05 13:25:47.333826000 +03:00 : +000000000 00:00:00.020822000
Start 3: 10.11.05 13:25:47.334226
End   3: 10.11.05 13:25:47.371349000 +03:00 : +000000000 00:00:00.037393000