Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to store XML data in a table in Oracle?
Problem Statement:
You need to store native XML data into a relational table in your database.
Solution:
Oracle have several ways of storing XML documents. One way of storing data where our XML doesn’t need to be altered, or where a portion of the XML can be extracted with XSLT, is to use XMLTYPE data casting.
We will use the XMLTYPE call to cast the text provided into the XMLTYPE datatype. In the background Oracle XMLTYPE supports CLOB datatype, because XML is stored internally as a CLOB. This means we can use the same approach to casting, passing the call to XMLTYPE a string up to 4GB in size.
Casting to XMLTYPE enforces some rules for our XML data. If the column or a table is defined using an XML schema, the schema will be used to validate the data, ensuring that mandatory elements are present and the overall structure maps exactly to the schema.
We will first create a table to store the XML.
CREATE TABLE tmp_store_xml (result XMLTYPE);
Code
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;
Output: One of the row from the table
<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>
Data Preparation: Data used for the problem is shown below. The data is completely made up for demonstration purpose.
Example
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) ) ;
Example
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;Example
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) ) ;
Example
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+ Views
