SELECT * FROM XMLTABLE('$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>20081130</DEAL_INURE_TIME>

                     <DEAL_EXPIRE_TIME>30000101</DEAL_EXPIRE_TIME>

                     <DEAL_CREATE_TIME>20081130</DEAL_CREATE_TIME>

                 </USER_DEAL_INFO>

                 <USER_DEAL_INFO>

                     <USER_DEAL_ID>1000100002</USER_DEAL_ID>

                     <DEAL_INURE_TIME>20081131</DEAL_INURE_TIME>

                     <DEAL_EXPIRE_TIME>30000102</DEAL_EXPIRE_TIME>

                     <DEAL_CREATE_TIME>20081131</DEAL_CREATE_TIME>

                 </USER_DEAL_INFO>

              </DEAL_BASIC>') 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')