数据结构
网络
关系数据库管理系统
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 编程
C++
C#
MongoDB
MySQL
Javascript
PHP
物理学
化学
生物学
数学
英语
经济学
心理学
社会学
时尚研究
法律研究如何在 Oracle 中将 XML 数据存储到表中？
问题陈述
您需要将本地 XML 数据存储到数据库中的关系表中。
解决方案
Oracle 有几种存储 XML 文档的方法。一种存储数据的方法是，我们的 XML 不需要更改，或者可以使用 XSLT 提取 XML 的一部分，即使用 XMLTYPE 数据转换。
我们将使用 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;
2K+ 浏览量
