Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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