ORACLE读取XML

第一个例子：

SELECT * FROM XMLTABLE('$T/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 T
    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'
    ) ;

第二个例子：

SELECT 'test',ROWNUM,sysdate+ROWNUM FROM  XMLTABLE('1 to 20');   

第三个例子：

WITH
StudentS AS (
SELECT 1 id, xmltype ('<Students>
    <Student 姓名="张小军" 职务="班长">
        <姓>张</姓>
        <名>小军</名>
        <年龄>11</年龄>
        <家长职业>无业</家长职业>
    </Student>
    <Student 姓名="王小明" 职务="体育委员">
        <姓>王</姓>
        <名>小明</名>
        <年龄>12</年龄>
        <家长职业>个体业主</家长职业>
    </Student>
    <Student 姓名="李小红" 职务="音乐课代表">
        <姓>李</姓>
        <名>小红</名>
        <年龄>13</年龄>
        <家长职业>大学讲师</家长职业>
    </Student>
    <Student 姓名="赵小亮" 职务="学生">
        <姓>赵</姓>
        <名>小亮</名>
        <年龄>14</年龄>
        <家长职业>自由职业者</家长职业>
    </Student>
</Students>') data FROM DUAL),
-- 从StudentS表解析XML内容
T1 AS (select t.id, x.*
from Students t,
xmltable('/Students/Student' passing t.data columns
姓 varchar2(30) path '姓',
名 varchar2(30) path '名',
年龄 varchar2(30) path '年龄',
家长职业 varchar2(30) path '家长职业') x
where t.id = 1),
-- 使用text()读取节点值
T2 AS (select t.id, x.*
from Students t,
xmltable('/Students/Student/姓' passing t.data columns
姓 varchar2(30) path 'text()') x
where t.id = 1),
-- 读取所选节点的属性
T3 AS (select emp.id, x.*
from Students emp,
xmltable('/Students/Student' passing emp.data columns
姓 varchar2(30) path '姓',  
名 varchar2(30) path '名',
年龄 varchar2(30) path '年龄',
家长职业 varchar2(30) path '家长职业',
职务 varchar2(30) path '@职务') x),
-- 使用ID读取特定的记录
T4 AS (select t.id, x.*
from Students t,
xmltable('/Students/Student[@姓名="赵小亮"]' passing t.data columns
姓 varchar2(30) path '姓',
名 varchar2(30) path '名',
年龄 varchar2(30) path '年龄',
家长职业 varchar2(30) path '家长职业') x
where t.id = 1
),
-- 读取所有类型是班长的学生的姓 和 名
T5 AS (select t.id, x.*
from Students t,
xmltable('/Students/Student[@职务="班长"]' passing t.data columns
姓 varchar2(30) path '姓',
名 varchar2(30) path '名',
年龄 varchar2(30) path '年龄',
家长职业 varchar2(30) path '家长职业') x
where t.id = 1
),
-- 读取年龄超过12的所有学生的姓 和 名
T6 AS (select t.id, x.*
from Students t,
xmltable('/Students/Student[年龄>12]' passing t.data columns
姓 varchar2(30) path '姓',
名 varchar2(30) path '名',
年龄 varchar2(30) path '年龄',
家长职业 varchar2(30) path '家长职业') x
where t.id = 1
)
-- SELECT * FROM T1 ;
-- SELECT * FROM T2 ;
-- SELECT * FROM T3 ;
-- SELECT * FROM T4 ;
-- SELECT * FROM T5 ;
SELECT * FROM T6 ;