Skip to Main Content

SQL & PL/SQL

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
263 views