http://huangronaldo.iteye.com/blog/1457567

some test~

declare
  v varchar2(2000);
  v_name  varchar2(2000);
  v_val  varchar2(2000);
  p_cur sys_refcursor;
  V_XML XMLTYPE;
begin
  pkg_xml.newXml;
  pkg_xml.newrow;
  pkg_xml.add('hello','ee');
  pkg_xml.add('haha','dd');
  p(pkg_xml.g_Xml.getStringval());
  v:=xmltype.extract(pkg_xml.g_XML,'/ROWSET/ROW').getStringval;
  P(V);
  select a.column_value.getrootelement() nam,
                       extractvalue(a.column_value, '*') val
                       into v_name,v_val
  from TABLE(xmlsequence(extract(pkg_xml.g_XML,'/ROWSET/ROW/HAHA'))) a;
  P('v_name:'||v_name);
  p('v_val:'||v_val);
  open p_cur for select table_name from user_tables WHERE ROWNUM<4;
  V_XML:=pkg_xml.cursor2XML( p_cur);
  p(V_Xml.getStringval());
end; 
/

SELECT existsnode(a.val,'/ROWSET/ROW/HAHA')  FROM (SELECT SYS_XMLAGG(SYS_XMLGEN(A.TABLE_NAME||STATUS)) val FROM USER_TABLES A) a;
SELECT existsnode(a.val,'/ROW'),a.*  FROM (SELECT SYS_XMLGEN(A.TABLE_NAME||STATUS) val FROM USER_TABLES A) a;
select * from TABLE(xmlsequence(extract(pkg_xml.newxml, '/'))) x;
with t as (select table_name from user_tables where rownum<4)
select * from t;
with t as (select table_name from user_tables where rownum<4)
select * from TABLE(xmlsequence(extract(pkg_xml.cursor2XML(t)))), '/')) x;

SELECT * FROM USER_TABLES;

SELECT SYS_XMLAGG(SYS_XMLGEN(A.TABLE_NAME||STATUS)) FROM USER_TABLES A;
SELECT SYS_XMLAGG(SYS_XMLGEN(A.VAL)) FROM (SELECT A.TABLE_NAME VAL FROM USER_TABLES A) A;
SELECT SYS_XMLAGG(XMLELEMENT("ROW",SYS_XMLGEN(A.VAL))) FROM (SELECT A.TABLE_NAME VAL FROM USER_TABLES A) A;
SELECT SYS_XMLAGG(SYS_XMLGEN(xmlelement("ROW",A.VAL))) FROM (SELECT A.TABLE_NAME VAL FROM USER_TABLES A) A;

SELECT SYS_XMLAGG(SYS_XMLGEN(A.VAL)) FROM (SELECT XMLELEMENT("ROW",A.TABLE_NAME) VAL FROM USER_TABLES A) A;

SELECT XMLFOREST(A.TABLE_NAME) FROM USER_TABLES A;
SELECT SYS_XMLGEN(A.TABLE_NAME) FROM USER_TABLES A;
SELECT XMLCOLATTVAL(A.TABLE_NAME,STATUS) FROM USER_TABLES A;
SELECT XMLELEMENT("ROW",XMLCOLATTVAL(A.TABLE_NAME,STATUS)) FROM USER_TABLES A;
SELECT XMLELEMENT("VAL",A.TABLE_NAME) FROM USER_TABLES A;

select xmlconcat(xmltype('1'),

                 xmltype('2'),

                 xmltype('3'))

  from dual;
 
   select updatexml(xmltype(
   '
     
          1
          NAME1
     
     
          2
          NAME2
     
    '),
                 '/root/row[no=2]/name',

                 'NEW_NAME')

  from dual;
 
 
  SELECT XMLTRANSFORM(XMLTYPE(''),XMLTYPE('')) FROM DUAL;

SELECT *
FROM   xmltable('/ROWSET/ROW' passing
                XMLTYPE('1ORACLE')
                columns "ID" NUMBER path 'ID',
                "NAME" VARCHAR2(10) path 'NAME');