1

I created an XMLType table

CREATE TABLE PERSON OF XMLType
  XMLTYPE store AS OBJECT RELATIONAL
  XMLSCHEMA "PERSON.XSD" ELEMENT "root";

The XML is -

<root>
<Person empid="1" empname="Joe" empproj="IT" emporg="Consulting" />
</root>

I created view for the XMLType table. But I could not see the values inserted in table in the view. I want to have the XML attributes (empid, empname, empproj, emporg) as columns of VIEW person_master_view for table PERSON.

CREATE OR REPLACE VIEW
  person_master_view(empid, empname, empproj, emporg)
AS SELECT xt.*
     FROM PERSON,
                XMLTable ('/root/Person' passing PERSON.object_value
                                 columns
                                     empid        number(10)   path '/Person',
                                     empname      varchar2(30) path '/Person',
                                     empproj      varchar2(30) path '/Person',
                                     emporg       varchar2(30) path '/Person'
                             ) as xt

1 Answer 1

1

The following worked -

CREATE OR REPLACE VIEW
      person_master_view(empid, empname, empproj, emporg)
    AS SELECT xt.*
         FROM PERSON P,
                    XMLTable ('//Person' passing P.object_value
                                     columns
                                         empid        number(10)   path '@empid',
                                         empname      varchar2(30) path '@empname',
                                         empproj      varchar2(30) path '@empproj',
                                         emporg       varchar2(30) path '@emporg'
                                 ) as xt

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.