--方法1
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');
--方法2
SELECT EXTRACTVALUE(VALUE(t), '/USER_DEAL_INFO/USER_DEAL_ID') i --编码
,
EXTRACTVALUE(VALUE(t), '/USER_DEAL_INFO/DEAL_INURE_TIME') t -- 类型
,
EXTRACTVALUE(VALUE(t), '/USER_DEAL_INFO/DEAL_EXPIRE_TIME') t -- 类型
,
EXTRACTVALUE(VALUE(t), '/USER_DEAL_INFO/DEAL_CREATE_TIME') t -- 类型
FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE( '&xml' ),
'/DEAL_BASIC/USER_DEAL_INFO'))) t;
--xml 源代码
<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>