--------------------创建临时表 临时保存从xml字符串解析来的数据---------------------------
会话级别临时表
SQL> create global temporary table temp_order01 (orderid number,ordernumber number,orderdate date) on commit delete rows;
Table created.
事务级别临时表
SQL> create global temporary table temp_order02 (orderid number,ordernumber number,orderdate date) on commit preserve rows;
Table created.
SQL> INSERT INTO TEMP_ORDER SELECT * FROM XMLTABLE('$B/orderlist/order' PASSING
XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&V_XML') AS B
COLUMNS orderid number PATH '/order/orderid',
ordernumber number PATH '/order/ordernumber',
orderdate date PATH '/order/orderprice');
Enter value for v_xml: <orderlist><order><orderid>1</orderid><ordernumber>01</ordernumber><orderdate>sysdate</orderdate></order></orderlist>
old 2: XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&V_XML') AS B
new 2: XMLTYPE('<?xml version="1.0" encoding="gb2312" ?><orderlist><order><orderid>1</orderid><ordernumber>01</ordernumber><orderdate>sysdate</orderdate></order></orderlist> ') AS B
1 row created.
SQL> select * from temp_order;
ORDERID ORDERNUMBER ORDERDATE
---------- ----------- ---------
1 1
SQL> commit;
Commit complete.
SQL> select * from temp_order;
no rows selected
<orderlist><order><orderid>1</orderid><ordernumber>01</ordernumber><orderdate>sysdate</orderdate></order></orderlist>