資料結構
網路
關係資料庫管理系統
作業系統
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+ 瀏覽量
