In the previous post we have discussed about generating an XML file using Oracle SQL XML functions. Today we will do that in reverse manner. That means we will load the data generated by that query in the database tables.
At the end of this post (Which is a continue of the previous post) - you will be successfully generate an XML file from Oracle Tables & also able to load the data from XML on that same structured tables. So, that will complete the full life cycle of XML in oracle(Obviously concentrate on some basics).
Lets see -
Our main ingredients for this class - is the XML file named - emp_oracle.xml
And, it looks like -
<?xml version="1.0" encoding="UTF-8"?> <EmployeeList> <Emp> <Employee_ID>200</Employee_ID> <First>Whalen</First> <Sal>4400</Sal> <HireDate>1987-09-17</HireDate> </Emp> <Emp> <Employee_ID>201</Employee_ID> <First>Billy</First> <Sal>4500</Sal> <HireDate>1985-06-10</HireDate> </Emp> <Emp> <Employee_ID>202</Employee_ID> <First>Bireswar</First> <Sal>9000</Sal> <HireDate>1978-06-10</HireDate> </Emp> </EmployeeList>We need to create one Oracle Directories to map with the Operating System directories in the following manner ->
sys@ORCL> sys@ORCL>select * from v$version; BANNER ---------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production Elapsed: 00:00:00.00 sys@ORCL> sys@ORCL> sys@ORCL>CREATE OR REPLACE DIRECTORY SATY_DIR AS 'D:\XML_Output' 2 / Directory created. Elapsed: 00:00:00.23 sys@ORCL> sys@ORCL>GRANT READ, WRITE ON DIRECTORY SATY_DIR TO SCOTT, HR; Grant succeeded. Elapsed: 00:00:00.08 sys@ORCL>
Once you have created the directory successfully and give the proper privileges to the users like Scott or Hr - you have completed one important component of today's test. Still we are far to go. Now the second part is -
scott@ORCL>
scott@ORCL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Elapsed: 00:00:00.00
scott@ORCL>
scott@ORCL>
scott@ORCL>CREATE TABLE t
2 (
3 serialNo NUMBER(10),
4 fileName VARCHAR2(100),
5 xml XMLTYPE,
6 constraints pk_serialNo primary key(serialNo)
7 );
Table created.
Elapsed: 00:00:04.13
scott@ORCL>
scott@ORCL>
scott@ORCL>CREATE SEQUENCE x_seq
2 START WITH 1
3 INCREMENT BY 1;
Sequence created.
Elapsed: 00:00:00.31
scott@ORCL>
scott@ORCL>CREATE OR REPLACE PROCEDURE load_xml(
2 p_dir IN VARCHAR2,
3 p_filename IN VARCHAR2
4 )
5 IS
6 l_bfile BFILE := BFILENAME(p_dir, p_filename);
7 l_clob CLOB;
8 BEGIN
9 DBMS_LOB.createtemporary (l_clob, TRUE);
10
11 DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
12 DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
13 DBMS_LOB.fileclose(l_bfile);
14
15 INSERT INTO t(
16 serialNo,
17 fileName,
18 xml
19 )
20 VALUES (
21 x_seq.NEXTVAL,
22 p_filename,
23 XMLTYPE.createXML(l_clob)
24 );
25
26 COMMIT;
27
28 DBMS_LOB.freetemporary(l_clob);
29 END;
30 /
Procedure created.
Elapsed: 00:00:00.88
scott@ORCL>
scott@ORCL>EXEC load_xml(p_dir => 'SATY_DIR', p_filename => 'emp_oracle.xml');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.16
scott@ORCL>
scott@ORCL>set long 5000
scott@ORCL>
scott@ORCL>set pagesize 0
scott@ORCL>
scott@ORCL>select xml from t;
<?xml version="1.0" encoding="UTF-8"?>
<EmployeeList>
<Emp>
<Employee_ID>200</Employee_ID>
<First>Whalen</First>
<Sal>4400</Sal>
<HireDate>1987-09-17</HireDate>
</Emp>
<Emp>
<Employee_ID>201</Employee_ID>
<First>Billy</First>
<Sal>4500</Sal>
<HireDate>1985-06-10</HireDate>
</Emp>
<Emp>
<Employee_ID>202</Employee_ID>
<First>Bireswar</First>
<Sal>9000</Sal>
<HireDate>1978-06-10</HireDate>
</Emp>
</EmployeeList>
Elapsed: 00:00:00.10
scott@ORCL>Ok. So, we've initially load the data into the temp table t. But, we need to load the data from this temp table t to our target table revive_xml which will look like -
scott@ORCL>create table revive_xml 2 ( 3 rev_emp_id number(4), 4 rev_f_name varchar2(40), 5 rev_salary number(10,2), 6 rev_jn_dt date, 7 constraints pk_rev_emp_id primary key(rev_emp_id) 8 ); Table created. Elapsed: 00:00:00.40 scott@ORCL>
Ok. So, we have done another important part of our job. Let's concentrate on our final mission -
scott@ORCL>insert into revive_xml(
2 rev_emp_id,
3 rev_f_name,
4 rev_salary,
5 rev_jn_dt
6 )
7 select cast(t1.EmployeeId as number(4)) EmployeeId,
8 t2.FirstName,
9 cast(t3.Salary as number(10,2)) Salary,
10 to_date(t4.JoiningDt,'YYYY-MM-DD') JoiningDt
11 from (
12 select rownum rn1,
13 extractValue(value(EmployeeId),'/Emp/Employee_ID') EmployeeId
14 from t,
15 table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) EmployeeId
16 ) t1,
17 (
18 select rownum rn2,
19 extractValue(value(FirstName),'/Emp/First') FirstName
20 from t,
21 table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) FirstName
22 ) t2,
23 (
24 select rownum rn3,
25 extractValue(value(Salary),'/Emp/Sal') Salary
26 from t,
27 table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) Salary
28 ) t3,
29 (
30 select rownum rn4,
31 extractValue(value(HireDate),'/Emp/HireDate') JoiningDt
32 from t,
33 table(xmlsequence(extract(xml, '/EmployeeList/Emp'))) HireDate
34 ) t4
35 where t1.rn1 = t2.rn2
36 and t1.rn1 = t3.rn3
37 and t1.rn1 = t4.rn4;
3 rows created.
Elapsed: 00:00:00.16
scott@ORCL>
scott@ORCL>commit;
Commit complete.
Elapsed: 00:00:00.22
scott@ORCL>
scott@ORCL>
scott@ORCL>select * from revive_xml;
REV_EMP_ID REV_F_NAME REV_SALARY REV_JN_DT
---------- ---------------------------------------- ---------- ---------
200 Whalen 4400 17-SEP-87
201 Billy 4500 10-JUN-85
202 Bireswar 9000 10-JUN-78
scott@ORCL>So, you have done it finally.
You can do it another way but that is limited to single record parsing -
scott@ORCL>with t
2 as (
3 select xmlType('
4 <EmployeeList>
5 <Emp>
6 <Employee_ID>200</Employee_ID>
7 <First>Whalen</First>
8 <Sal>4400</Sal>
9 <HireDate>1987-09-17</HireDate>
10 </Emp>
11 </EmployeeList>') xml from dual
12 )
13 SELECT rownum rn,
14 a.EmployeeId,
15 a.FirstName,
16 a.Salary,
17 a.JoiningDt
18 FROM t,
19 XMLTABLE('/EmployeeList'
20 PASSING t.xml
21 COLUMNS
22 EmployeeId varchar2(10) PATH '/EmployeeList/Emp/Employee_ID',
23 FirstName varchar2(20) PATH '/EmployeeList/Emp/First',
24 Salary number(10) PATH '/EmployeeList/Emp/Sal',
25 JoiningDt date PATH '/EmployeeList/Emp/HireDate'
26 ) a;
RN EMPLOYEEID FIRSTNAME SALARY JOININGDT
---------- ---------- -------------------- ---------- ---------
1 200 Whalen 4400 17-SEP-87
scott@ORCL>
scott@ORCL>Hope this will solve your purpose.
Also you can refer to the following XML In Oracle link.
Regards.