--XMLTABLE中的to变量只能是小写
--或者用FROM dual connect by rownum<=3;
CREATE OR REPLACE PROCEDURE PKG_AUTOCREATETABLEANDDATA(
PN_COL_NUM NUMBER DEFAULT 0,
PN_ROW_NUM NUMBER DEFAULT 0,
PC_TABLE_NAME OUT VARCHAR2)
AS
LN_COL_NUM NUMBER;
LN_ROW_NUM NUMBER;
LC_TABLE_NAME VARCHAR2(30);
LC_COLUMN_TYPE_LIST VARCHAR2(30000):='|VARCHAR2(LNG)<DBMS_RANDOM.STRING(''U'',LNG)>|NUMBER<DBMS_RANDOM.VALUE(-99999999999999,+99999999999999)>|DATE<SYSDATE-DBMS_RANDOM.VALUE(-100000,100000)>|';
LC_TABLE_CREATE VARCHAR2(30000);
LC_TABLE_INSERT VARCHAR2(30000);
LC_TMP_COLTYPE VARCHAR2(30000);
LC_TMP_DATA VARCHAR2(30000);
LN_TMP_LENGTH NUMBER;
LN_TMP_NB NUMBER;
BEGIN
FOR K IN 1..100 LOOP
LC_TABLE_CREATE:='';
LC_TABLE_INSERT:='';
IF PN_COL_NUM BETWEEN 1 AND 5 THEN
LN_COL_NUM:=PN_COL_NUM;
ELSE
LN_COL_NUM:=ROUND(DBMS_RANDOM.VALUE(1,5));
END IF;
IF PN_ROW_NUM BETWEEN 1 AND 5 THEN
LN_ROW_NUM:=PN_ROW_NUM;
ELSE
LN_ROW_NUM:=ROUND(DBMS_RANDOM.VALUE(1,1000));
END IF;
LC_TABLE_NAME:='TETB_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||DBMS_RANDOM.STRING('U',11);
FOR I IN 1..LN_COL_NUM LOOP
--GET COLUMN TYPE
LN_TMP_NB:=LENGTH(REPLACE(LC_COLUMN_TYPE_LIST,'|','||'))-LENGTH(LC_COLUMN_TYPE_LIST)-1;
LN_TMP_NB:=ROUND(DBMS_RANDOM.VALUE(1,LN_TMP_NB));
LC_TMP_COLTYPE:=SUBSTR(LC_COLUMN_TYPE_LIST,INSTR(LC_COLUMN_TYPE_LIST,'|',1,LN_TMP_NB)+1);
LC_TMP_DATA:=SUBSTR(LC_TMP_COLTYPE,INSTR(LC_TMP_COLTYPE,'<')+1);
LC_TMP_DATA:=SUBSTR(LC_TMP_DATA,1,INSTR(LC_TMP_DATA,'>')-1);
LC_TMP_COLTYPE:=SUBSTR(LC_TMP_COLTYPE,1,INSTR(LC_TMP_COLTYPE,'<')-1);
--VARCHAR2 LENGTH
IF INSTR(LC_TMP_DATA,'LNG')>0 OR INSTR(LC_TMP_COLTYPE,'LNG')>0 THEN
LN_TMP_LENGTH:=ROUND(DBMS_RANDOM.VALUE(1,1500));
LC_TMP_DATA:=REPLACE(LC_TMP_DATA,'LNG',LN_TMP_LENGTH);
LC_TMP_COLTYPE:=REPLACE(LC_TMP_COLTYPE,'LNG',LN_TMP_LENGTH);
END IF;
LC_TABLE_CREATE:=LC_TABLE_CREATE||', COL'||I||' '||LC_TMP_COLTYPE;
LC_TABLE_INSERT:=LC_TABLE_INSERT||', '||LC_TMP_DATA;
END LOOP;
--LC_TABLE_CREATE:='CREATE TABLE '||LC_TABLE_NAME||' ('||SUBSTR(LC_TABLE_CREATE,2)||')';
LC_TABLE_CREATE:='CREATE TABLE '||LC_TABLE_NAME||' (ID NUMBER PRIMARY KEY,'||SUBSTR(LC_TABLE_CREATE,2)||')';
LC_TABLE_INSERT:='INSERT INTO '||LC_TABLE_NAME||' SELECT ROWNUM,'||SUBSTR(LC_TABLE_INSERT,2)||' FROM XMLTABLE(''1 to '||LN_ROW_NUM||''')';
PC_TABLE_NAME:=LC_TABLE_NAME||' COL:'||LN_COL_NUM||' ROW:'||LN_ROW_NUM;
EXECUTE IMMEDIATE LC_TABLE_CREATE;
EXECUTE IMMEDIATE LC_TABLE_INSERT;
COMMIT;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(LC_TABLE_CREATE);
DBMS_OUTPUT.PUT_LINE(LC_TABLE_INSERT);
RAISE_APPLICATION_ERROR(-20010,SQLCODE||'--'||SQLERRM);
END PKG_AUTOCREATETABLEANDDATA;
/
--赋予权限
GRANT CREATE TABLE TO USER_NAME;
--------------------------------------------------------------------------------------------------------------------------------------------
--查询生成的表和数据
SELECT 'SELECT * FROM '||T.TABLE_NAME||';','DROP TABLE '||T.TABLE_NAME||';',T.* FROM USER_TABLES T WHERE T.TABLE_NAME LIKE '%TETB%';
--生成表和数据
DECLARE
v_table_name VARCHAR2(500);
BEGIN
PKG_autocreatetableanddata(2,2,pc_table_name =>v_table_name);
END;
/
--删除表
DECLARE
V_EXE VARCHAR2(1000);
BEGIN
FOR REC IN (SELECT 'DROP TABLE '||T.TABLE_NAME AS V_EXE FROM USER_TABLES T WHERE T.TABLE_NAME LIKE '%TETB%') LOOP
EXECUTE IMMEDIATE REC.V_EXE;
END LOOP;
END;
/
-------------------------------------------------------------------------------------------------------------------------------------------------------
--Oracle XMLTable
从Oracle 10g开始，甲骨文公司新增了XQuery和XMLTable两个功能作为处理XML的武器。 XMLQuery一样，您可以使用XQuery语言构造XML数据和查询XML和关系数据。你可以使用XMLTable从XQuery查询结果创建关系表和列。
本文我们将了解Oracle XMLTable函数，并且通过例子介绍XMLTable函数的用法。
考虑到员工会有一些XML数据，所以我们创建一个EMPLOYEES表：
Create TABLE EMPLOYEES
(
id NUMBER,
data XMLTYPE
)
表创建完成后，我们往表里插入一些数据:
Insert INTO EMPLOYEES
VALUES (1, xmltype ('<Employees>
<Employee emplid="1111" type="admin">
<firstname>John</firstname>
<lastname>Watson</lastname>
<age>30</age>
<email>johnwatson@sh.com</email>
</Employee>
<Employee emplid="2222" type="admin">
<firstname>Sherlock</firstname>
<lastname>Homes</lastname>
<age>32</age>
<email>sherlock@sh.com</email>
</Employee>
<Employee emplid="3333" type="user">
<firstname>Jim</firstname>
<lastname>Moriarty</lastname>
<age>52</age>
<email>jim@sh.com</email>
</Employee>
<Employee emplid="4444" type="user">
<firstname>Mycroft</firstname>
<lastname>Holmes</lastname>
<age>41</age>
<email>mycroft@sh.com</email>
</Employee>
</Employees>'));
注意：XML包含了员工的相关数据，在我们开始学习之前我们首先明确几个数据：
1、有4名员工在我们的XML文件
2、每个员工都有通过属性定义一个唯一的员工id emplid
3、每个员工也有一个属性type，定义雇员是否是管理员或用户。
4、每个员工都有四个子节点： firstname ， lastname ， age和email
5、年龄是多少
现在我们可以使用Oracle XMLTable函数从XML中检索不同的信息。
1、学习XPath表达式
使用XMLTable函数之前最好知道一点关于XPath。XPath使用路径表达式来选择XML文档中的节点或节点列表。看下面的列表：
Expression
Description
nodename
选择所有名称为"nodename"的节点
/
选择根节点
//
从当前节点选择文档中相匹配的节点，无论他们在哪里
.
选择当前节点
..
选择当前节点的父节点
@
选择属性
employee
选择所有名称为"employee"的节点
employees/employee
选择所有子节点为employee的employees节点
//employee
选择所有employee的元素，无论他们在哪里
下面的表达式称为谓词列表。谓词在方括号中定义 [ ... ]。他们被用来找到一个特定的节点或包含一个特定值的节点。
Path Expression
Result
/employees/employee[1]
选择第一个employee节点,它是employees的子节点。
/employees/employee[last()]
选择最后一个employee元素，它是employees的子节点
/employees/employee[last()-1]
选择是employees子元素的倒数第二个employee元素
//employee[@type='admin']
选择所有具有与'admin'的值的属性命名类型的employee元素
其他更多的表达式可以参考Oracle官方手册
2、Oracle XMLTable函数的基础知识
读取Employees中所有firstname和lastname
在这个查询中,我们使用XMLTable函数从EMPLOYEES表解析XML内容。
select t.id, x.*
from employees t,
xmltable('/Employees/Employee' passing t.data columns firstname
varchar2(30) path 'firstname',
lastname varchar2(30) path 'lastname') x
where t.id = 1;
注XMLTable函数的语法：
XMLTable('<XQuery>'
PASSING <xml column>
COLUMNS <new column name> <column type> PATH <XQuery path>)
XMLTABLE函数包含一个XQuery行表达式和由一个或多个列表达式组成的COLUMNS子句。在上面的语句中，行表达式是 XPath /Employees/Employee。PASSING子句中的t.data指的是employees表中的XML列中的数据。
COLUMNS 子句用于将XML数据转换成关系数据，这里每个参数都定义了一个列名和SQL数据类型。在上面的查询中，我们定义了firstname 和 lastname列并指向PATH的firstname 和 lastname或者选定的节点。
输出：
使用text()读取节点值
在上面的教程中，我们读取到了firstname / lastname节点。通常我们还需要获取节点的文本值，下面的例子中，我们选取/Employees/Employee/firstname路径，并使用text()获取节点的值。
下面查询employees中所有的firstname
select t.id, x.*
from employees t,
xmltable('/Employees/Employee/firstname' passing t.data columns
firstname varchar2(30) path 'text()') x
where t.id = 1
输出：
不仅仅是text()表达式，Oracle还提供了其他很多有用的表达式，如 item(), node(), attribute(), element(), document-node(), namespace(), text(), xs:integer, xs:string。
读取所选节点的属性
XML节点定了相关属性，我们也可以读取到节点的这些属性，下面的查询是找出employee节点的type属性：
select emp.id, x.*
from employees emp,
xmltable('/Employees/Employee' passing emp.data columns firstname
varchar2(30) path 'firstname',
type varchar2(30) path '@type') x;
输出：
使用ID读取特定的记录
select t.id, x.*
from employees t,
xmltable('/Employees/Employee[@emplid=2222]' passing t.data columns
lastname varchar2(30) path 'lastname') x
where t.id = 1;
输出：
读取所有类型是admin的员工的firstname 和 lastname
select t.id, x.*
from employees t,
xmltable('/Employees/Employee[@type="admin"]' passing t.data columns
firstname varchar2(30) path 'firstname',
lastname varchar2(30) path 'lastname') x
where t.id = 1;
输出：
读取年龄超过40的所有员工的firstname 和 lastname
select t.id, x.*
from employees t,
xmltable('/Employees/Employee[age>40]' passing t.data columns
firstname varchar2(30) path 'firstname',
lastname varchar2(30) path 'lastname',
age varchar2(30) path 'age') x
where t.id = 1;
输出：
本文由UncleToo翻译整理，转载请注明出处！
原文（英文）地址：http://viralpatel.net/blogs/oracle-xmltable-tutorial/