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');
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11990065/viewspace-757881/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11990065/viewspace-757881/