Hi
I have tables like schem HR (clone)
I used only 3 in example the names table are changed
TBL_LOCATION,
TBL_DEPT
TBL_EMPLOYEES
CREATE TABLE tbl_location
( location_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
) ;
/
CREATE UNIQUE INDEX tbl_loc_id_pk
ON tbl_location (location_id) ;
/
ALTER TABLE tbl_location
ADD ( CONSTRAINT tbl_loc_id_pk
PRIMARY KEY (location_id)
) ;
/
CREATE TABLE tbl_dept
( department_id NUMBER(4)
, department_name VARCHAR2(30),
location_id NUMBER(4)
) ;
/
CREATE UNIQUE INDEX tbl_dept_id_pk
ON tbl_dept (department_id) ;
/
ALTER TABLE tbl_dept
ADD ( CONSTRAINT tbl_dept_id_pk
PRIMARY KEY (department_id)
, CONSTRAINT tbl_dept_loc_fk
FOREIGN KEY (location_id)
REFERENCES tbl_location (location_id)
) ;
/
CREATE TABLE tbl_employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
, manager_id NUMBER(6)
, department_id NUMBER(4)
) ;
/
CREATE UNIQUE INDEX tbl_emp_emp_id_pk
ON tbl_employees (employee_id) ;
/
ALTER TABLE tbl_employees
ADD ( CONSTRAINT tbl_emp_emp_id_pk
PRIMARY KEY (employee_id)
, CONSTRAINT tbl_emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES tbl_dept
, CONSTRAINT tbl_emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES tbl_employees
DEFERRABLE INITIALLY DEFERRED
) ;
/
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1000, '1297 Via Cola di Rie', '00989');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1100, '93091 Calle della Testa', '10934');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1200, '2017 Shinjuku-ku', '1689');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1300, '9450 Kamiya-cho', '6823');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1400, '2014 Jabberwocky Rd', '26192');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1500, '2011 Interiors Blvd', '99236');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1600, '2007 Zagora St', '50090');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1700, '2004 Charade Rd', '98199');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1800, '147 Spadina Ave', 'M5V 2L7');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (1900, '6092 Boxwood St', 'YSW 9T2');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (2000, '40-5-12 Laogianggen', '190518');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (2100, '1298 Vileparle (E)', '490231');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (2200, '12-98 Victoria Street', '2901');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (2300, '198 Clementi North', '540198');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS) Values (2400, '8204 Arthur St');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (2600, '9702 Chester Road', '09629850293');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (2700, 'Schwanthalerstr. 7031', '80925');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (2800, 'Rua Frei Caneca 1360 ', '01307-002');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (2900, '20 Rue des Corps-Saints', '1730');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (3000, 'Murtenstrasse 921', '3095');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (3100, 'Pieter Breughelstraat 837', '3029SK');
Insert into TBL_LOCATION (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values (3200, 'Mariano Escobedo 9991', '11932');
COMMIT;
Insert into TBL_DEPT (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (20, 'Marketing', 1800);
Insert into TBL_DEPT (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (30, 'Purchasing', 1700);
Insert into TBL_DEPT (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (60, 'IT', 1400);
Insert into TBL_DEPT (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (90, 'Executive', 1700);
COMMIT;
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID) Values (100, 'Steven', 'King', 90);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (201, 'Michael', 'Hartstein', 100, 20);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (114, 'Den', 'Raphaely', 100, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (101, 'Neena', 'Kochhar', 100, 90);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (102, 'Lex', 'De Haan', 100, 90);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (103, 'Alexander', 'Hunold', 102, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (105, 'David', 'Austin', 103, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (104, 'Bruce', 'Ernst', 103, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (106, 'Valli', 'Pataballa', 103, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (107, 'Diana', 'Lorentz', 103, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (119, 'Karen', 'Colmenares', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (117, 'Sigal', 'Tobias', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (115, 'Alexander', 'Khoo', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (116, 'Shelli', 'Baida', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (118, 'Guy', 'Himuro', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values (202, 'Pat', 'Fay', 201, 20);
COMMIT;
I would like to create a XML like below
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<EMPLOY CAPTA="TRUE">
<FUNC CMD="VOID">
<LOCAL>
<location_id>1700</<location_id>
<DEPTO>
<department_id>30</department_id>
<EMPLOY>
<EMPLOYEE_ID>114 </EMPLOYEE_ID>
<FIRST_NAME>Den</FIRST_NAME>
<LAST_NAME>Raphaely</LAST_NAME>
</EMPLOY>
<EMPLOY>
<EMPLOYEE_ID>115</EMPLOYEE_ID>
<FIRST_NAME>Alexander</FIRST_NAME>
<LAST_NAME>Khoo</LAST_NAME>
</EMPLOY>
<EMPLOY>
<EMPLOYEE_ID>116 </EMPLOYEE_ID>
<FIRST_NAME>Shelli</FIRST_NAME>
<LAST_NAME>Baida</LAST_NAME>
</EMPLOY>
<EMPLOY>
<EMPLOYEE_ID>117 </EMPLOYEE_ID>
<FIRST_NAME>Sigal</FIRST_NAME>
<LAST_NAME>Tobias</LAST_NAME>
</EMPLOY>
<EMPLOY>
<EMPLOYEE_ID>118</EMPLOYEE_ID>
<FIRST_NAME>Guy</FIRST_NAME>
<LAST_NAME>Himuro</LAST_NAME>
</EMPLOY>
<EMPLOY>
<EMPLOYEE_ID>119</EMPLOYEE_ID>
<FIRST_NAME>Karen</FIRST_NAME>
<LAST_NAME>Colmenares</LAST_NAME>
</EMPLOY>
</DEPTO>
<DEPTO>
<department_id>90</department_id>
<EMPLOY>
<EMPLOYEE_ID>100</EMPLOYEE_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
</EMPLOY>
<EMPLOY>
<EMPLOYEE_ID>101</EMPLOYEE_ID>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
</EMPLOY>
<EMPLOY>
<EMPLOYEE_ID>102 </EMPLOYEE_ID>
<FIRST_NAME>Lex</FIRST_NAME>
<LAST_NAME>De Haan</LAST_NAME>
</EMPLOY>
</DEPTO>
</LOCAL>
</FUNC>
</EMPLOY>
I tried the query below, but I do know how can to do It
SELECT E.EMPLOYEE_ID,
E.FIRST_NAME ,
E.LAST_NAME ,
J.DEPARTMENT_ID
FROM TBL_EMPLOYEES E
INNER JOIN TBL_DEPT J
ON E.DEPARTMENT_ID = J.DEPARTMENT_ID
INNER JOIN TBL_LOCATION L
ON J.LOCATION_ID = L.LOCATION_ID
WHERE L.LOCATION_ID = 1700
START WITH E.MANAGER_ID IS NULL
CONNECT BY PRIOR E.EMPLOYEE_ID = E.MANAGER_ID
Using
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Thank you in advance