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

select * from v$nls_valid_values where parameter = 'CHARACTERSET';

SQL> select updateXml(
   2              XMLType('<?xml version="1.0" encoding="Cp1251"?><ROOT><USERS/></ROOT>')
   3            , '/ROOT/USERS'
   4            ,  XmlElement("USERS", XMLAGG(XmlForest(user_id, username)))
   5          ) xml
   6   from all_users
   7   where username = user;

XML
----------------------------------------------------------------------------------------
<?xml version="1.0" encoding="WINDOWS-1251"?>
<ROOT>
  <USERS>
    <USER_ID> 60 </USER_ID>
    <USERNAME>SCOTT</USERNAME>
  </USERS>
</ROOT>


SQL> select updateXml(
   2              XMLType('<?xml version="1.0" encoding="Cp1251"?><ROOT><USERS/></ROOT>')
   3            , '/ROOT/USERS'
   4            ,  XmlElement("USERS", XMLAGG(XmlForest(user_id, username)))
   5          ).getStringVal() xml
   6   from all_users
   7   where username = user;

XML
----------------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
  <USERS>
    <USER_ID> 60 </USER_ID>
    <USERNAME>SCOTT</USERNAME>
  </USERS>
</ROOT>