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!

Generate XML from tables

muttleychessNov 7 2018 — edited Nov 8 2018

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

Philip Stoyanov-Oracle

The problem is in processing of check constraints - it seems there is a check constraint without condition. Can you run query for check constraints and to see in what column is the condition and if there is a check constraint without condition

Philip

NISHANTH BEJGAM

Thank you so much Philip. I followed your instruction and by executing the following queries, I see no results returned. and validated the CHECK constraints with conditions as well. Besides, I see couple of constraints are in disabled state in scheme now, please confirm will that cause the issue.
select constraint_name,search_condition from user_constraints where constraint_type='C' and search_condition is NULL;
select constraint_name,search_condition from all_constraints where constraint_type='C' and search_condition is NULL
select constraint_name,search_condition from dba_constraints where constraint_type='C' and search_condition is NULL.

Philip Stoyanov-Oracle

there is one more column SEARCH_CONDITION_VC - can you check it as well?

NISHANTH BEJGAM

all the constraints are having NULL value for SEARCH_CONDITION_VC however SEARCH_CONDITION does has the value in it.

Philip Stoyanov-Oracle

what's the DB version, PDB or CDB?

NISHANTH BEJGAM

it is 12C, PDB

Philip Stoyanov-Oracle

I cannot reproduce in my environment. How those tables and check constraints are created. Do you connect as owner of those tables or you see them through grants and synonyms. Are they in another container/PDB?

1 - 7

Added on Nov 7 2018

#xml

8 comments

329 views

SQL & PL/SQL

Generate XML from tables

Comments

Post Details