首页 > 代码库 > Oracle使用Sql把XML解析成表(Table)的方法

Oracle使用Sql把XML解析成表(Table)的方法

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)

结果:

技术分享

参:Oracle使用Sql把XML解析成表(Table)的方法

 

Oracle使用Sql把XML解析成表(Table)的方法