1 Reply Latest reply: Dec 26, 2013 10:22 AM by odie_63 RSS

Oracle XML DB Using Views

user594708 Newbie
Currently Being Moderated

I created a view for XMLType table. When I insert rows into table they are not reflected in view.

 

CREATE TABLE EMPLOYEE OF XMLType

  XMLTYPE store AS OBJECT RELATIONAL

  XMLSCHEMA "EMPLOYEE.XSD" ELEMENT "root";

 

 

  CREATE OR REPLACE VIEW

  employee_master_view(empid, empname, empproj, emporg)

AS SELECT extractValue(OBJECT_VALUE, '/EMPLOYEE/empid'),

          extractValue(OBJECT_VALUE, '/EMPLOYEE/empname'),

          extractValue(OBJECT_VALUE, '/EMPLOYEE/empproj'),

          extractValue(OBJECT_VALUE, '/EMPLOYEE/emporg')

     FROM EMPLOYEE;

 

INSERT INTO EMPLOYEE VALUES (XMLType(bfilename('XML_DIR', 'EMPLOYEE.XML'),

                                     nls_charset_id('AL32UTF8')));

 

SELECT object_value from employee;

OBJECT_VALUE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

---------------------------------------------

SYS.XMLTYPE

 

SELECT extract (object_value, '/') from employee;

EXTRACT(OBJECT_VALUE,'/')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<root><empid>1</empid><empname>Gates</empname><empproj>EIS</empproj><emporg>Tata Cosultancy Services</emporg></root>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

 

1 rows selected

 

desc employee_master_view;

Name                       Null Type                                                                                                                                                                                    

------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EMPID                               VARCHAR2()                                                                                                                                                                              
EMPNAME                             VARCHAR2()                                                                                                                                                                              
EMPPROJ                             VARCHAR2()                                                                                                                                                                              
EMPORG                              VARCHAR2()                                                                                                                                                                              

 

4 rows selected

 

select empid, empname, empproj, emporg from employee_master_view;

EMPID     EMPNAME     EMPPROJ     EMPORG

-----------    ----------------      ----------------    ----------------


1 rows selected

 

This returns empty rows. It should be printing the values of the xml tags in the columns.

 

Please help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points