6 Replies Latest reply: Feb 5, 2013 2:49 PM by Kevin_K RSS

    XML document into Object Relational tables

    Kevin_K
      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
          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
            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
              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
                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
                  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
                    MDrake: Thanks that works!