如下XML,oracle存储过程如何解析?求大神解答

发布网友发布时间：2022-04-08 09:26

共2个回答

懂视网时间：2022-04-08 13:48

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‘);

费了千辛万苦，终于有了一点门路

oracle解析xml完成版

标签：

热心网友时间：2022-04-08 10:56

create procere funWen
@Temperature int,
@qiwen varchar(10) output
as
begin
if @Temperature in (25,26,27)
set @qiwen='常温'
else if @Temperature in (23,24)
set @qiwen='低温'
else if @Temperature in (28,29)
set @qiwen='高温'
else
set @qiwen='温度不正常'
end

--调用
Declare @qiwen varchar(10)
exec funWen 27,@qiwen output
print @qiwen