This discussion is archived
6 Replies Latest reply: Feb 5, 2013 12:49 PM by Kevin_K RSS

XML document into Object Relational tables

Kevin_K Newbie
Currently Being Moderated
Experts: I followed several examples online and was able to insert an XML document into an Oracle Object Relational Table . I see that Oracle creates a table behind the scenes and a few data types which map to the elements in the xsd file.

My assumption was that I can run plain selects
( select col1, col2, col3 from OR_XML_Table )
against the inserted xml document columns lbut infact I have to still xpath into the oracle object relational table to get access to the element values.

Is there any way to insert the xml elements into Oracle Relational tables which can be queried without xpath expressions.
Oracle db 11.2.0.3
Linux 5
Thanks
Kev

Edited by: Kevin_K on Feb 5, 2013 12:50 PM
  • 1. Re: Shred XML document into Object Relational tables
    mdrake Expert
    Currently Being Moderated
    Simply use XMLTable to create a set of relational views that map the XPATH expressions to columns. As long as these tables are backed by an OR storage model we will automatically re-write queries against the views in queries on the underlying tables.. That said, never attempt to access the underlying tables directly...
  • 2. Re: Shred XML document into Object Relational tables
    Kevin_K Newbie
    Currently Being Moderated
    Mdrake: Thanks for your suggestion.
    CREATE TABLE PERSON_XML OF SYS.XMLTYPE 
    XMLTYPE STORE AS OBJECT RELATIONAL
    XMLSCHEMA "person.xsd"
    ELEMENT "Person"
    
    
    CREATE OR REPLACE VIEW person_view OF XMLType
     WITH OBJECT ID (XMLCast(XMLQuery('/Person/@FirstName'
                                       PASSING OBJECT_VALUE RETURNING CONTENT)
                              AS VARCHAR2(100))) AS
    select
    extractValue(OBJECT_VALUE, '/Person/FirstName') as first_name, 
    extractValue(OBJECT_VALUE, '/Person/LastName') as last_name,
    extractValue(OBJECT_VALUE, '/Person/DateOfBirth')  as dob    
         from Person_xml     
    where 
         extractValue(OBJECT_VALUE, '/Person/FirstName') = 'Kevin'
    I get an error "ORA-01730: invalid number of column names specified"

    I am very close . please suggest what am I not doing right?

    Thanks
    Kev
  • 3. Re: Shred XML document into Object Relational tables
    odie_63 Guru
    Currently Being Moderated
    I get an error "ORA-01730: invalid number of column names specified"

    I am very close . please suggest what am I not doing right?
    The error is pretty self-explanatory.
    The query projects three columns, but you're trying to build an object view (with a single object column).

    You're also using deprecated functions.
    CREATE OR REPLACE VIEW person_view as
    select x.*
    from Person_xml t
       , xmltable(
           '/Person'
           passing t.object_value
           columns First_Name  varchar2(30) path 'FirstName'
                 , Last_Name   varchar2(30) path 'LastName'
                 , dob         date         path 'DateOfBirth'
         ) x
    -- where x.first_name = 'Kevin'
     ;
    (adjust the datatypes as necessary)
  • 4. Re: Shred XML document into Object Relational tables
    Kevin_K Newbie
    Currently Being Moderated
    My data is stored in the XML OR table in this date format:

    1971-05-18T00:00:00.000000

    I am able to create the view but get
    ORA-01830: date format picture ends before converting entire input string
    
    CREATE OR REPLACE VIEW person_view as
    select x.*
    from Person_xml t
       , xmltable(
           '/Person'
           passing t.object_value
           columns First_Name  varchar2(30) path 'FirstName'
                 , Last_Name   varchar2(30) path 'LastName'
                 , dob         date         path 'DateOfBirth'
         ) x
     ;
     
    Is there any oracle provided function to convert this to oracle supported date format?

    Thanks
    Kev
  • 5. Re: Shred XML document into Object Relational tables
    mdrake Expert
    Currently Being Moderated
    I suspect you have a timestamp or timestamp with time zone in your XML. You will need to use the approprate data type in the XMLTAble and then do an explicit cast in the SQL select list if you want date
  • 6. Re: Shred XML document into Object Relational tables
    Kevin_K Newbie
    Currently Being Moderated
    MDrake: Thanks that works!

Legend

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