解析xml

SELECT * FROM XMLTABLE('$B/ROW' PASSING
 XMLTYPE('<ROW>
 <TP_ID>112</TP_ID>
 <TP_NAME>ORACLE</TP_NAME>
 <TP_PARENTID>0</TP_PARENTID>
 <TP_SNAME>R</TP_SNAME>
</ROW>') AS B
 COLUMNS
 TP_ID NUMBER     PATH '/ROW/TP_ID',
 TP_NAME VARCHAR2(50) PATH '/ROW/TP_NAME',
 TP_PARENTID NUMBER  PATH '/ROW/TP_PARENTID',
 TP_SNAME VARCHAR2(50) PATH '/ROW/TP_SNAME');

SELECT EXTRACTVALUE(VALUE(t),'/USER_DEAL_INFO/USER_DEAL_ID')  g   
           ,EXTRACTVALUE(VALUE(t),'/USER_DEAL_INFO/DEAL_INURE_TIME')  o   
           ,EXTRACTVALUE(VALUE(t),'/USER_DEAL_INFO/DEAL_EXPIRE_TIME')   p  
           ,EXTRACTVALUE(VALUE(t),'/USER_DEAL_INFO/DEAL_CREATE_TIME') l
        
    FROM TABLE(XMLSEQUENCE(EXTRACT(
    XMLTYPE('
    <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>'), '/DEAL_BASIC/USER_DEAL_INFO'))) t;    -- 提取XML节点值