如何在 Oracle 表中存储 XML 数据?
问题描述:
您需要将原生 XML 数据存储到数据库的关系表中。
解决方案:
Oracle 提供了多种存储 XML 文档的方法。其中一种存储方式是使用 XMLTYPE 数据转换，这种方式不需要修改 XML 数据，或者 XML 数据的一部分可以使用 XSLT 提取。
我们将使用 XMLTYPE 调用将提供的文本转换为 XMLTYPE 数据类型。在后台，Oracle XMLTYPE 支持 CLOB 数据类型，因为 XML 在内部存储为 CLOB 类型。这意味着我们可以使用相同的方法进行转换，将调用 XMLTYPE 的字符串传递给最大 4GB 的字符串。
转换为 XMLTYPE 会对我们的 XML 数据强制执行一些规则。如果使用 XML 模式定义列或表，则该模式将用于验证数据，确保必需元素存在且整体结构与模式完全映射。
我们首先创建一个表来存储 XML。
CREATE TABLE tmp_store_xml (result XMLTYPE);
代码
DECLARE result XMLTYPE; data VARCHAR2(10); BEGIN FOR CUR IN (SELECT department_id FROM departments) LOOP WITH tmp AS (SELECT XMLROOT(XMLFOREST( dept_t(department_id, department_name, CAST(MULTISET (SELECT student_id, first_name, last_name, phone_number FROM students e WHERE e.department_id = d.department_id ) AS stulist_t )) AS "Department"),version '1.0') AS dataxml FROM departments d WHERE d.department_id = '' || cur.department_id || '' ) SELECT XMLTYPE.CREATEXML(XMLSERIALIZE(CONTENT (dataxml) INDENT size=2)) INTO result FROM tmp; INSERT INTO tmp_store_xml VALUES(result); COMMIT; END LOOP; END;
输出:表中的一行
<Department DEPTNO="60"> <DNAME>IT</DNAME> <STU_LIST> <STU_T STUNO="103"> <FNAME>BROWN</FNAME> <LNAME>MICHAEL</LNAME> <PHONE>111.111.1248</PHONE> </STU_T> <STU_T STUNO="104"> <FNAME>JONES</FNAME> <LNAME>WILLIAM</LNAME> <PHONE>111.111.1249</PHONE> </STU_T> <STU_T STUNO="105"> <FNAME>MILLER</FNAME> <LNAME>DAVID</LNAME> <PHONE>111.111.1250</PHONE> </STU_T> <STU_T STUNO="106"> <FNAME>DAVIS</FNAME> <LNAME>RICHARD</LNAME> <PHONE>111.111.1251</PHONE> </STU_T> <STU_T STUNO="107"> <FNAME>GARCIA</FNAME> <LNAME>CHARLES</LNAME> <PHONE>111.111.1252</PHONE> </STU_T> </STU_LIST> </Department>
数据准备:本问题所用数据如下。数据完全出于演示目的而编造。
示例
DROP TABLE students; COMMIT; CREATE TABLE students ( student_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) , email VARCHAR2(40) , phone_number VARCHAR2(20) , join_date DATE , class_id VARCHAR2(20) , fees NUMBER(8,2) , professor_id NUMBER(6) , department_id NUMBER(4) ) ;
示例
CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);
COMMIT;
示例
CREATE TABLE departments ( department_id NUMBER(4) , department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL , professor_id NUMBER(6) , location_id NUMBER(4) ) ;
示例
INSERT INTO departments VALUES ( 10, 'Administration', 200, 1700); INSERT INTO departments VALUES ( 20, 'Teaching', 201, 1800); INSERT INTO departments VALUES ( 30 , 'Purchasing' , 114 , 1700 ); INSERT INTO departments VALUES ( 40 , 'Human Resources' , 203 , 2400 ); INSERT INTO departments VALUES ( 50 , 'Students' , 121 , 1500 ); INSERT INTO departments VALUES ( 60 , 'IT' , 103 , 1400 ); INSERT INTO departments VALUES ( 70 , 'Public Relations' , 204 , 2700 ); INSERT INTO departments VALUES ( 80 , 'Fee collectors' , 145 , 2500 ); INSERT INTO departments VALUES ( 90 , 'Executive' , 100 , 1700 ); INSERT INTO departments VALUES ( 100 , 'Finance' , 108 , 1700 ); INSERT INTO departments VALUES ( 110 , 'Accounting' , 205 , 1700 ); INSERT INTO departments VALUES ( 120 , 'Treasury' , NULL , 1700 ); INSERT INTO departments VALUES ( 130 , 'Corporate Tax' , NULL , 1700 ); INSERT INTO departments VALUES ( 140 , 'Control And Credit' , NULL , 1700 ); INSERT INTO departments VALUES ( 160 , 'Benefits' , NULL , 1700 ); INSERT INTO departments VALUES ( 230 , 'Helpdesk' , NULL , 1700 ); COMMIT;
