Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Generate XML from tables

muttleychessNov 7 2018 — edited Nov 8 2018

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

This post has been answered by Paulzip on Nov 7 2018
Jump to Answer
Comments
Post Details
Added on Nov 7 2018
8 comments
326 views