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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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.