No question at this time
DBA Top 10
1 M. Cadot 17000
2 A. Kavsek 14300
3 B. Vroman 10300
4 M. Hidayathullah ... 8400
5 T. Boles 4900
6 P. Wisse 4400
7 J. Schnackenberg 3500
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 800
9 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48097
Total active users1702
Act. users last 24h1
Act. users last hour0
Registered user hits last week237
Registered user hits last month1339
Go up

XML parsing and inserting data into multiple tables using
Next thread: OCP Upgrade to Oracle Database 12c Exam Guide (Exam 1Z0-060)
Prev thread: Sequence in RAC

Message Score Author Date
Hi I need to insert data into Location,Department,...... ss0061933 ss0061933 Jun 06, 2015, 11:11
Post the CREATE TABLE for the target tables so w...... Michel Cadot Jun 06, 2015, 14:11
CREATE TABLE LOCATIONS ( LOCATION_ID NUMBE...... ss0061933 ss0061933 Jun 06, 2015, 17:59
PLEASE IGNORE PREVIOUS ddl's , as it contains synt...... ss0061933 ss0061933 Jun 06, 2015, 18:03
OK, now put the INSERT statements for the data t...... Michel Cadot Jun 06, 2015, 18:40
<pre>SQL> insert all 2 when dep_pos = 1 and ...... Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts Michel Cadot Jun 06, 2015, 19:48
Result: <pre>SQL> select * from locations; LOCAT...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Michel Cadot Jun 06, 2015, 19:49
Hi , Thanks for your quick reply. Solution is pe...... ss0061933 ss0061933 Jun 07, 2015, 05:44
Yes it is possible just long to write... <pre>s...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Michel Cadot Jun 07, 2015, 09:31
Yes got it , able to code it using other syntax to...... ss0061933 ss0061933 Jun 07, 2015, 15:25
It is up to you. I prefer a single INSERT it is...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Michel Cadot Jun 07, 2015, 16:52
Yes I Preffered individual insert into tables . A...... ss0061933 ss0061933 Jun 10, 2015, 12:58

Follow up by mail Click here


Subject: XML parsing and inserting data into multiple tables using
Author: ss0061933 ss0061933, India
Date: Jun 06, 2015, 11:11, 925 days ago
Oracle info: oracle 11g
Message: Hi I need to insert data into Location,Department,employee tables (maintaining parent child relation) i.e. record should get inserted into location first and then department if location exists and then employee.
Sample data-
<Locations>
<Location id="1400">
<address>2014 Jabberwocky Rd</address>
<postalCode>26192</postalCode>
<city>Southlake</city>
<stateProvince>Texas</stateProvince>
<countryId>US</countryId>
<Departments>
<Department id="60">
<name>IT</name>
<managerId>103</managerId>
<Employees>
<Employee id="103">
<firstName>Alexander</firstName>
<lastName>Hunold</lastName>
<email>AHUNOLD</email>
<phoneNo>590.423.4567</phoneNo>
<hireDate>2006-01-03</hireDate>
<jobId>IT_PROG</jobId>
<salary>9000</salary>
<managerId>102</managerId>
</Employee>
<Employee id="104">
<firstName>Bruce</firstName>
<lastName>Ernst</lastName>
<email>BERNST</email>
<phoneNo>590.423.4568</phoneNo>
<hireDate>2007-05-21</hireDate>
<jobId>IT_PROG</jobId>
<salary>6000</salary>
<managerId>103</managerId>
</Employee>
</Employees>
</Department>
</Departments>
</Location>
</Locations>


Can someone please help me with this?
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: Michel Cadot, France
Date: Jun 06, 2015, 14:11, 925 days ago
Message:
Post the CREATE TABLE for the target tables so we can work with.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: ss0061933 ss0061933, India
Date: Jun 06, 2015, 17:59, 924 days ago
Message: CREATE TABLE LOCATIONS
(
LOCATION_ID NUMBER(4),
STREET_ADDRESS VARCHAR2(40 BYTE),
POSTAL_CODE VARCHAR2(12 BYTE),
CITY VARCHAR2(30 BYTE) ,
STATE_PROVINCE VARCHAR2(25 BYTE),
COUNTRY_ID CHAR(2 BYTE)
);
ALTER TABLE HR.LOCATIONS ADD (
CONSTRAINT LOC_ID_PK
PRIMARY KEY
(LOCATION_ID)
USING INDEX HR.LOC_ID_PK
ENABLE VALIDATE);
CREATE TABLE HR.DEPARTMENTS
(
DEPARTMENT_ID NUMBER(4),
DEPARTMENT_NAME VARCHAR2(30 BYTE) CONSTRAINT DEPT_NAME_NN NOT NULL,
MANAGER_ID NUMBER(6),
LOCATION_ID NUMBER(4)
);
ALTER TABLE HR.DEPARTMENTS ADD (
CONSTRAINT DEPT_ID_PK
PRIMARY KEY
(DEPARTMENT_ID));
ALTER TABLE HR.DEPARTMENTS ADD (
CONSTRAINT DEPT_LOC_FK
FOREIGN KEY (LOCATION_ID)
REFERENCES HR.LOCATIONS (LOCATION_ID));

CREATE TABLE HR.EMPLOYEES
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,
EMAIL VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL,
PHONE_NUMBER VARCHAR2(20 BYTE),
HIRE_DATE DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,
JOB_ID VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);

ALTER TABLE HR.EMPLOYEES ADD
CONSTRAINT EMP_EMP_ID_PK
PRIMARY KEY
(EMPLOYEE_ID);

ALTER TABLE HR.EMPLOYEES ADD (
CONSTRAINT EMP_DEPT_FK
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES HR.DEPARTMENTS (DEPARTMENT_ID));

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: ss0061933 ss0061933, India
Date: Jun 06, 2015, 18:03, 924 days ago
Message: PLEASE IGNORE PREVIOUS ddl's , as it contains syntax error.
Use Below-

CREATE TABLE LOCATIONS
(
LOCATION_ID NUMBER(4),
STREET_ADDRESS VARCHAR2(40 BYTE),
POSTAL_CODE VARCHAR2(12 BYTE),
CITY VARCHAR2(30 BYTE) ,
STATE_PROVINCE VARCHAR2(25 BYTE),
COUNTRY_ID CHAR(2 BYTE)
);
ALTER TABLE LOCATIONS ADD (
CONSTRAINT LOC_ID_PK
PRIMARY KEY
(LOCATION_ID)
);
CREATE TABLE DEPARTMENTS
(
DEPARTMENT_ID NUMBER(4),
DEPARTMENT_NAME VARCHAR2(30 BYTE) CONSTRAINT DEPT_NAME_NN NOT NULL,
MANAGER_ID NUMBER(6),
LOCATION_ID NUMBER(4)
);
ALTER TABLE DEPARTMENTS ADD (
CONSTRAINT DEPT_ID_PK
PRIMARY KEY
(DEPARTMENT_ID));
ALTER TABLE DEPARTMENTS ADD (
CONSTRAINT DEPT_LOC_FK
FOREIGN KEY (LOCATION_ID)
REFERENCES LOCATIONS (LOCATION_ID));

CREATE TABLE EMPLOYEES
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,
EMAIL VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL,
PHONE_NUMBER VARCHAR2(20 BYTE),
HIRE_DATE DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,
JOB_ID VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);

ALTER TABLE EMPLOYEES ADD
CONSTRAINT EMP_EMP_ID_PK
PRIMARY KEY
(EMPLOYEE_ID);

ALTER TABLE EMPLOYEES ADD (
CONSTRAINT EMP_DEPT_FK
FOREIGN KEY (DEPARTMENT_ID)
REFERENCES DEPARTMENTS (DEPARTMENT_ID));
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: Michel Cadot, France
Date: Jun 06, 2015, 18:40, 924 days ago
Message:
OK, now put the INSERT statements for the data that should exist before inserting the new one you gave in your first post.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: Michel Cadot, France
Date: Jun 06, 2015, 19:48, 924 days ago
Score:   Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts
Message:
SQL> insert all

2 when dep_pos = 1 and emp_pos = 1 then
3 into locations (location_id,street_address,postal_code,city)
4 values (location_id,street_address,postal_code,city)
5 when emp_pos = 1 then
6 into departments (department_id,department_name,manager_id,location_id)
7 values (department_id,department_name,dep_manager_id,location_id)
8 into employees (employee_id,first_name,last_name,email,phone_number,hire_date,
9 job_id,salary,manager_id,department_id)
10 values (employee_id,first_name,last_name,email,phone_number,
11 to_date(hire_date,'YYYY-MM-DD'),job_id,salary,emp_manager_id,
12 department_id)
13 select *
14 from (select '
15 <Locations>
16 <Location id="1400">
17 <address>2014 Jabberwocky Rd</address>
18 <postalCode>26192</postalCode>
19 <city>Southlake</city>
20 <stateProvince>Texas</stateProvince>
21 <countryId>US</countryId>
22 <Departments>
23 <Department id="60">
24 <name>IT</name>
25 <managerId>103</managerId>
26 <Employees>
27 <Employee id="103">
28 <firstName>Alexander</firstName>
29 <lastName>Hunold</lastName>
30 <email>AHUNOLD</email>
31 <phoneNo>590.423.4567</phoneNo>
32 <hireDate>2006-01-03</hireDate>
33 <jobId>IT_PROG</jobId>
34 <salary>9000</salary>
35 <managerId>102</managerId>
36 </Employee>
37 <Employee id="104">
38 <firstName>Bruce</firstName>
39 <lastName>Ernst</lastName>
40 <email>BERNST</email>
41 <phoneNo>590.423.4568</phoneNo>
42 <hireDate>2007-05-21</hireDate>
43 <jobId>IT_PROG</jobId>
44 <salary>6000</salary>
45 <managerId>103</managerId>
46 </Employee>
47 </Employees>
48 </Department>
49 </Departments>
50 </Location>
51 </Locations>' val from dual) v,
52 xmltable('/Locations/Location' passing xmltype(v.val)
53 columns
54 "LOC_POS" FOR ORDINALITY,
55 "LOCATION_ID" NUMBER(4) PATH '@id',
56 "STREET_ADDRESS" VARCHAR2(40) PATH '/Location/address',
57 "POSTAL_CODE" VARCHAR2(12) PATH '/Location/postalCode',
58 "CITY" VARCHAR2(30) PATH '/Location/city',
59 "STATE_PROVINCE" VARCHAR2(25) PATH '/Location/stateProvince',
60 "COUNTRY_ID" CHAR(2) PATH '/Location/countryId',
61 "DEPARTMENTS" XMLTYPE PATH '/Location/Departments') x,
62 xmltable('/Departments/Department' passing x.departments
63 columns
64 "DEP_POS" FOR ORDINALITY,
65 "DEPARTMENT_ID" NUMBER(4) PATH '@id',
66 "DEPARTMENT_NAME" VARCHAR2(30) PATH '/Department/name',
67 "DEP_MANAGER_ID" NUMBER(6) PATH '/Department/managerId',
68 "EMPLOYEES" XMLTYPE PATH '/Department/Employees') y,
69 xmltable('/Employees/Employee' passing y.employees
70 columns
71 "EMP_POS" FOR ORDINALITY,
72 "EMPLOYEE_ID" NUMBER(6) PATH '@id',
73 "FIRST_NAME" VARCHAR2(20) PATH '/Employee/firstName',
74 "LAST_NAME" VARCHAR2(25) PATH '/Employee/lastName',
75 "EMAIL" VARCHAR2(25) PATH '/Employee/email',
76 "PHONE_NUMBER" VARCHAR2(20) PATH '/Employee/phoneNo',
77 "HIRE_DATE" VARCHAR2(20) PATH '/Employee/hireDate',
78 "JOB_ID" VARCHAR2(10) PATH '/Employee/jobId',
79 "SALARY" NUMBER(8,2) PATH '/Employee/salary',
80 "EMP_MANAGER_ID" NUMBER(6) PATH '/Employee/managerId') z
81 /

3 rows created.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: Michel Cadot, France
Date: Jun 06, 2015, 19:49, 924 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Result:
SQL> select * from locations;

LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY
----------- ---------------------------------------- ------------ ------------------------------
STATE_PROVINCE CO
------------------------- --
1400 2014 Jabberwocky Rd 26192 Southlake


1 row selected.

SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
60 IT 103 1400

1 row selected.

SQL> select * from employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER
----------- -------------------- ------------------------- ------------------------- --------------------
HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
------------------- ---------- ---------- -------------- ---------- -------------
103 Alexander Hunold AHUNOLD 590.423.4567
03/01/2006 00:00:00 IT_PROG 9000 102 60

1 row selected.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: ss0061933 ss0061933, India
Date: Jun 07, 2015, 05:44, 924 days ago
Message: Hi ,
Thanks for your quick reply.
Solution is perfect!!

I was struggling to get the output since I was trying with help of syntax-

select Extract(value(p),'Locations/...)
FROM TABLE(XMLSequence(Extract(xmltype('') , ' ' )P

As in your query you inside xmltable x(i.e. Location details), you are passing DEPARTMENTS as xmltpye which is then used to get corresponding department details and then similar for employees.

Can you please advise if same can be acheived using above syntax?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: Michel Cadot, France
Date: Jun 07, 2015, 09:31, 924 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message:
Yes it is possible just long to write...
select Extract(value(p),'Locations/...)

FROM TABLE(XMLSequence(Extract(xmltype('/Locations...') , '/Locations/Location' )P,
table(XMLSequence(value(p) , '/Location/Departments/Department' ) q,
table(XMLSequence(value(q) , '/Department/Employees/Employee' ) r
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: ss0061933 ss0061933, India
Date: Jun 07, 2015, 15:25, 924 days ago
Message: Yes got it , able to code it using other syntax too :).

One more query,
For better error handling I am thinking to parse and insert data one by one into Locations, Departments and Employees table. It will help me to report exact error (i.e. record which is causing failure).
Example lets see Locations and Departments table data is correct but issue with Employees data . with insert all it will be difficult for me to trace it.
Instead if i do :-
select Location details
Insert into Locations
..Error handling
select department details
Insert into departments
..Error Handling
select Employees details
Insert into Employees
..Error Handling
Will this approach will be good?

As I have to give meaningful error message to front end user who is inserting details through Java forms,
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: Michel Cadot, France
Date: Jun 07, 2015, 16:52, 923 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message:
It is up to you.
I prefer a single INSERT it is faster but you can do it in multiple ones if you want to report a more detailed and specific error message; note this will be less efficient. I advise you do it in a PL/SQL block to ensure the atomicity of the whole XML data.
You can also use BULL COLLECT and FORALL if you want even more detailed error messages; one level less efficient but allow you to detail the erroneous part of the XML.

Note that anyway you can't have a more specific message than the one generated by the XMLTYPE function if the input XML is not valid XML.

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: XML parsing and inserting data into multiple tables using
Author: ss0061933 ss0061933, India
Date: Jun 10, 2015, 12:58, 921 days ago
Message: Yes I Preffered individual insert into tables .
As XML contains information of specific Location -departrment at a time so performance is not concern .
Also customer needed detail error message. So handled it acoordingly in procedure.


Thanks for your help :)
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here