SELECT * fromXMLTABLE ('$B/deal_basic/user_deal_info'Passing XMLTYPE ('<?xml version= "1.0" encoding= "gb2312"?> <DEAL_BASIC> <USER_DEAL_INFO> <USER_DEAL_ID>1000100001</USER_DEAL_ID> <deal_inure_time>2008113 0</deal_inure_time> <DEAL_EXPIRE_TIME>30000101</DEAL_EXPIRE_TIME> <DEAL_CREATE_TIME>20081130</DEAL_CREATE_TIME> </USER_DEAL_INFO> <us Er_deal_info> <USER_DEAL_ID>1000100002</USER_DEAL_ID> <deal_inur E_time>20081131</deal_inure_time> <DEAL_EXPIRE_TIME>30000102</DEAL_EXPIRE_TIME> <DEAL_CREATE_TIME>20081131</DEAL_CREATE_TIME> </USER_DEAL_INFO> </DEAL_BASIC>') asB COLUMNS user_deal_idVARCHAR2( -) PATH'/user_deal_info/user_deal_id', Deal_inure_timeVARCHAR2( -) PATH'/user_deal_info/deal_inure_time', Deal_expire_timeVARCHAR2( -) PATH'/user_deal_info/deal_expire_time', Deal_create_timeVARCHAR2( -) PATH'/user_deal_info/deal_create_time')
Results:
Reference: Oracle uses SQL to parse XML into tables (table) methods
Oracle uses SQL to parse XML into tables (table) methods