SELECT * FROM XMLTABLE('$B/DEAL_BASIC/USER_DEAL_INFO' PASSING XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&xml') AS B COLUMNS USER_DEAL_ID VARCHAR2(50) PATH '/USER_DEAL_INFO/USER_DEAL_ID', DEAL_INURE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_INURE_TIME', DEAL_EXPIRE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_EXPIRE_TIME', DEAL_CREATE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/DEAL_CREATE_TIME'); <DEAL_BASIC> <USER_DEAL_INFO> <USER_DEAL_ID>1000100001</USER_DEAL_ID> <DEAL_INURE_TIME>20081130</DEAL_INURE_TIME> <DEAL_EXPIRE_TIME>30000101</DEAL_EXPIRE_TIME> <DEAL_CREATE_TIME>20081130</DEAL_CREATE_TIME> </USER_DEAL_INFO> </DEAL_BASIC>----------------------------------------------------------案例一 完成的
SELECT * FROM XMLTABLE('$B/ROW' PASSING XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&xml') AS B COLUMNS TP_ID VARCHAR2(50) PATH '/ROW/TP_ID', TP_NAME VARCHAR2(50) PATH '/ROW/TP_NAME', TP_PARENTID VARCHAR2(50) PATH '/ROW/TP_PARENTID', TP_SNAME VARCHAR2(50) PATH '/ROW/TP_SNAME'); <ROW> <TP_ID>112</TP_ID> <TP_NAME>oracle</TP_NAME> <TP_PARENTID>0</TP_PARENTID> <TP_SNAME>R</TP_SNAME></ROW>------------------------------------------------------------------------案例二 单引号 不能识别
SELECT * FROM XMLTABLE('$B/DEAL_BASIC/USER_DEAL_INFO' PASSING XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&xml') AS B COLUMNS USER_DEAL_ID VARCHAR2(50) PATH '/USER_DEAL_INFO/ROW_ID', DEAL_INURE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/ROW_TIME', DEAL_EXPIRE_TIME VARCHAR2(50) PATH '/USER_DEAL_INFO/ROW_NAME'); <DEAL_BASIC> <USER_DEAL_INFO> <ROW_ID>1000100001</ROW_ID> <ROW_TIME>ORACLE</ROW_TIME> <ROW_NAME>30000101</ROW_NAME> </USER_DEAL_INFO> <USER_DEAL_INFO> <ROW_ID>1000100001</ROW_ID> <ROW_TIME>ORACLE</ROW_TIME> <ROW_NAME>30000101</ROW_NAME> </USER_DEAL_INFO> </DEAL_BASIC> ----------------------------------------------------------------------案例三 完成的SELECT * FROM XMLTABLE('$B/orderlist/order' PASSING XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>&xml') AS B COLUMNS orderid VARCHAR2(50) PATH '/order/orderid', ordernumber VARCHAR2(50) PATH '/order/ordernumber', orderpriceE VARCHAR2(50) PATH '/order/orderprice');
<orderlist> <order> <orderid>1</orderid> <ordernumber>857544544</ordernumber> <orderprice>54</orderprice> </order> <order> <orderid>2</orderid> <ordernumber>858544544</ordernumber> <orderprice>63</orderprice> </order> <order> <orderid>3</orderid> <ordernumber>454854555</ordernumber> <orderprice>781</orderprice> </order> </orderlist>
费了千辛万苦 ，终于有了一点门路
转载于:https://www.cnblogs.com/iyoume2008/p/4788356.html
相关资源：oracle过程解析XML，支持超4000