17,086
社区成员
发帖
与我相关
我的任务
分享
with action as
(SELECT *
FROM XMLTABLE('$B/GET_DESCRIPTIONOutput' PASSING XMLTYPE('<?xml version="1.0" encoding="gb2312" ?>
<GET_DESCRIPTIONOutput >
<P_H> <p_shipment>121212</p_shipment>
<P_SHIP_DATA>2016-02-01</P_SHIP_DATA>
<P_RECITE_DATA>2016-02-04</P_RECITE_DATA>
</P_H>
<P_D>
<DT>
<item_id>520038396</item_id>
<unit_qty>1</unit_qty>
</DT>
<DT>
<item_id>520038394</item_id>
<unit_qty>2</unit_qty>
</DT>
</P_D>
</GET_DESCRIPTIONOutput>
') as b COLUMNS p_shipment VARCHAR2(50) PATH
'/GET_DESCRIPTIONOutput/P_H/p_shipment',
P_SHIP_DATA VARCHAR2(50) PATH
'/GET_DESCRIPTIONOutput/P_H/P_SHIP_DATA',
P_RECITE_DATA VARCHAR2(50) PATH
'/GET_DESCRIPTIONOutput/P_H/P_RECITE_DATA',
XT_DT xmltype path 'P_D/DT'))
select s. p_shipment,
s.P_SHIP_DATA,
s.P_RECITE_DATA,
xmltable('/DT/item_id' passing s.XT_DT columns item_id VARCHAR2(400) path
'item_id',
unit_qty VARCHAR2(400) path 'unit_qty')
from action s;