ORACLE解析XML数据成一个TABLE

ORACLE解析XML数据成一个TABLE

ORACLE解析XML数据

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

 

posted on 2012-08-03 17:34 钻石眼泪 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/huanghai223/archive/2012/08/03/2622015.html