oracle解析xml完成版

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

  • 0
    点赞
  • 0
    收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
评论
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值