1 Reply Latest reply: Dec 26, 2013 12:22 PM by odie_63 RSS

    Oracle XML DB Using Views

    user594708

      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.