This discussion is archived
7 Replies Latest reply: Nov 20, 2012 1:55 PM by MarcoGralike RSS

xmltable: defining columns datatype from table

917785 Newbie
Currently Being Moderated
Hi everyone

I'm using ORACLE 11g and looking to shred XML into a test table called employees. I was hoping I'd be able to get the datatypes from the existing employees table instead of specifying them in the columns clause. Is this possible?

Here is an example of what I'm trying to do. But, I get an error: PL/SQL: ORA-00907: missing right parenthesis on the line with starting with columns.
    insert into EMPLOYEES
     select *
       from xmltable(
       '/employees/employee'
        passing EMP_XML

        columns FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE path 'first_name',
                LAST_NAME  EMPLOYEES.LAST_NAME%TYPE  path 'last_name',
                GENDER     EMPLOYEES.GENDER%TYPE     path 'gender',
                AGE        EMPLOYEES.AGE%TYPE        path 'age'
        );
Error Details
        columns FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE path 'first_name',
                            *          

ERROR at line 16:
ORA-06550: line 16, column 42:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 11, column 5:
PL/SQL: SQL Statement ignored
Thanks.
  • 1. Re: xmltable: defining columns datatype from table
    odie_63 Guru
    Currently Being Moderated
    Hi,
    Is this possible?
    No, datatypes of the projected columns have to be hardcoded.

    There's a special case though, when the input XML document is schema-based (Object-Relational storage), you can omit the type declaration and the mapping will be inferred from the schema.
  • 2. Re: xmltable: defining columns datatype from table
    917785 Newbie
    Currently Being Moderated
    Thanks. Was really hoping it were possible.

    I've been trying to use an xml schema instead of declaring datatypes, but struggling. I put the code I've been working with below that may give a better idea of what I'd like to do ultimately.

    It seems as though the names from the schema aren't able to be used (ie, e.first_name). When I try to use them I get an error: PL/SQL: ORA-00904: "E"."FIRST_NAME": invalid identifier.

    I've searched around, but haven't seen anyone using an xml schema this way or much documentation on it. I have a feeling my code is totally off -- could you point the issue or refer me to a link that would be very useful?

    Thanks again!
       dbms_xmlschema.registerschema(
          schemaURL => 'employees.xsd',
          schemaDoc => xmltype(bfilename('DIR','employees.xsd'), nls_charset_id('AL32UTF8')),
          local => true,
          genTypes => true,
          genTables => false,
          enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
         );
    
    MERGE INTO EMPLOYEES s
    USING (select *
           from xmltable
           (
              xmlnamespaces(default 'employees.xsd'), '/employees/employee'
             passing xmltype(bfilename('DIR','employees.xml'), nls_charset_id('AL32UTF8'))
           )
          ) e
    ON (s.first_name = e.first_name)
    WHEN MATCHED
      THEN UPDATE SET s.last_name = e.last_name, s.gender = e.gender, s.age = e.age
    WHEN NOT MATCHED
      THEN INSERT VALUES(e.first_name,e.last_name,e.gender,e.age);
  • 3. Re: xmltable: defining columns datatype from table
    odie_63 Guru
    Currently Being Moderated
    Specifying column names is mandatory, but you can omit datatypes declaration.

    See : XMLTABLE SQL/XML Function in Oracle XML DB
    When XMLTable is used with XML schema-based storage of XMLType, datatype is optional. If not present, the data type is inferred from the XML schema. If Oracle > XML DB is unable to determine the proper type for a node, a default type of VARCHAR2(4000) is used.

    This is an Oracle extension; in the SQL/XML standard, datatype is always required.

    Note:
    The inferred data type might change as the result of upgrading Oracle XML DB or applying a patch. In particular, a new release or patch set might be able to > determine the data type when the previous release was unable to do so (and so defaulted to VARCHAR2(4000)). To protect against such an eventuality, specify an explicit data type with datatype.
  • 4. Re: xmltable: defining columns datatype from table
    917785 Newbie
    Currently Being Moderated
    Thanks so much!

    It ran error free (table wasn't updated, but I'll investigate to figure out why).

    Here is what I ended up with...
    MERGE INTO EMPLOYEES s
    USING (select *
           from xmltable
           (
              xmlnamespaces(default 'employees.xsd'), '/employees/employee'
             passing xmltype(bfilename('DIR','employees.xml'), nls_charset_id('AL32UTF8'))
             columns FIRST_NAME path 'first_name',
                     LAST_NAME  path 'last_name',
                     GENDER     path 'gender',
                     AGE        path 'age'
             
           )
          ) e
    ON (s.first_name = e.first_name)
    WHEN MATCHED
      THEN UPDATE SET s.last_name = e.last_name, s.gender = e.gender, s.age = e.age
    WHEN NOT MATCHED
      THEN INSERT VALUES(e.first_name,e.last_name,e.gender,e.age);
  • 5. Re: xmltable: defining columns datatype from table
    odie_63 Guru
    Currently Being Moderated
    Marseille wrote:
    Here is what I ended up with...
    In this case, all datatypes will be inferred as VARCHAR2(4000) so maybe not what you're after.

    Datatype inference only works when we query a persistent schema-based XML document, i.e. when it is stored in a schema-based table.

    Some examples :

    employees.xml
    <employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
     xsi:noNamespaceSchemaLocation="employees.xsd">
     <employee>
      <name>John</name>
      <hiredate>2010-06-05</hiredate>
      <salary>2000</salary>
     </employee>
     <employee>
      <name>Jack</name>
      <hiredate>2012-02-29</hiredate>
      <salary>2100</salary>
     </employee>
    </employees>
    SQL> begin
      2    dbms_xmlschema.registerSchema(
      3      schemaURL => 'employees.xsd'
      4    , schemaDoc =>
      5  '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
      6    <xs:element name="employees">
      7      <xs:complexType>
      8        <xs:sequence>
      9          <xs:element name="employee" maxOccurs="unbounded">
     10            <xs:complexType>
     11              <xs:sequence>
     12                <xs:element name="name" type="xs:string"/>
     13                <xs:element name="hiredate" type="xs:date"/>
     14                <xs:element name="salary" type="xs:integer"/>
     15              </xs:sequence>
     16            </xs:complexType>
     17          </xs:element>
     18        </xs:sequence>
     19      </xs:complexType>
     20    </xs:element>
     21  </xs:schema>'
     22    , local => true
     23    , genTypes => false
     24    , genTables => false
     25    , options => dbms_xmlschema.REGISTER_BINARYXML
     26    );
     27  end;
     28  /
     
    PL/SQL procedure successfully completed
     
    SQL> select "name", dump("name")
      2       , "hiredate", dump("hiredate")
      3       , "salary", dump("salary")
      4  from xmltable(
      5         '/employees/employee'
      6         passing xmltype(bfilename('TEST_DIR','employees.xml'), nls_charset_id('AL32UTF8'))
      7         columns "name", "hiredate", "salary"
      8       )
      9  ;
     
    name          DUMP("NAME")                       hiredate        DUMP("HIREDATE")                               salary    DUMP("SALARY")
    ------------- ---------------------------------- --------------- ---------------------------------------------- --------- ---------------------------
    John          Typ=1 Len=4: 74,111,104,110        2010-06-05      Typ=1 Len=10: 50,48,49,48,45,48,54,45,48,53    2000      Typ=1 Len=4: 50,48,48,48
    Jack          Typ=1 Len=4: 74,97,99,107          2012-02-29      Typ=1 Len=10: 50,48,49,50,45,48,50,45,50,57    2100      Typ=1 Len=4: 50,49,48,48
     
    See, all datatypes are of VARCHAR2 (typ=1).

    When first storing the file in a table :
    SQL> create table employees_xml of xmltype
      2  xmlschema "employees.xsd" element "employees"
      3  ;
     
    Table created
     
    SQL> insert into employees_xml values(
      2   xmltype(bfilename('TEST_DIR','employees.xml'), nls_charset_id('AL32UTF8'))
      3  );
     
    1 row inserted
     
    SQL> select "name", dump("name")
      2       , "hiredate", dump("hiredate")
      3       , "salary", dump("salary")
      4  from employees_xml t
      5     , xmltable(
      6         '/employees/employee'
      7         passing t.object_value
      8         columns "name", "hiredate", "salary"
      9       )
     10  ;
     
    name          DUMP("NAME")                       hiredate    DUMP("HIREDATE")                            salary DUMP("SALARY")
    ------------- ---------------------------------- ----------- --------------------------------------- ---------- ----------------------
    John          Typ=1 Len=4: 74,111,104,110        05/06/2010  Typ=12 Len=7: 120,110,6,5,1,1,1               2000 Typ=2 Len=2: 194,21
    Jack          Typ=1 Len=4: 74,97,99,107          29/02/2012  Typ=12 Len=7: 120,112,2,29,1,1,1              2100 Typ=2 Len=2: 194,22
     
    Now we get the correct datatypes VARCHAR2 (typ=1), DATE (typ=12) and NUMBER (typ=2).
  • 6. Re: xmltable: defining columns datatype from table
    917785 Newbie
    Currently Being Moderated
    Thanks, odie -- this was extremely helpful. I think with this post it finally sunk in. I'll most likely have to hard-code the datatypes because of limitations in my environment (i.e., create table restriction).

    The way I understand it now (based on your post above) is the only way to use the schema to set datatypes is to
    1. register the schema
    2. create a table based on the registered schema
    3. load in xml doc

    So, when I tried to use the schema without the associated table it used the default VARCHAR2(4000) and didn't use the schema at all (w/ and w/o schema I received the same output). I realize I'm parakeeting, but I wanted to be sure I understand completely.
  • 7. Re: xmltable: defining columns datatype from table
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Tried to see what would happen if I would extend the xmltype with createschemabasedXML('employees.xsd') but in memory the XML Parser keeps seeing it the object type 1 (varchar2), which makes sense but...there is a schema available...maybe something Oracle could improve...

    So still you would need the create table privilege to make it happen.

    The following is an alternative for Odie's example.
    /* ------------------------------
    
    select /*+ NO_XML_QUERY_REWRITE */
           "name", dump("name")
         , "hiredate", dump("hiredate")
         , "salary", dump("salary")
    from xmltable(
           XMLNAMESPACES('employees.xsd' AS "xs"),
           '/employees/employee'
           passing xmltype(bfilename('TEST_DIR','employees.xml'), nls_charset_id('AL32UTF8')).createSchemabasedXML('employees.xsd')
           columns "name", "hiredate", "salary"
         );
    
    ------------------------------ */
    SQL> begin
      2    dbms_xmlschema.registerSchema(
      3      schemaURL => 'employees.xsd'
      4    , schemaDoc =>
      5  '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
      6              xmlns:xdb="http://xmlns.oracle.com/xdb">
      7    <xs:element name="employees" xdb:defaultTable="EMPLOYEES_XML">
      8      <xs:complexType>
      9        <xs:sequence>
     10          <xs:element name="employee" maxOccurs="unbounded">
     11            <xs:complexType>
     12              <xs:sequence>
     13                <xs:element name="name" type="xs:string"/>
     14                <xs:element name="hiredate" type="xs:date"/>
     15                <xs:element name="salary" type="xs:integer"/>
     16              </xs:sequence>
     17            </xs:complexType>
     18          </xs:element>
     19        </xs:sequence>
     20      </xs:complexType>
     21    </xs:element>
     22  </xs:schema>'
     23    , local => true
     24    , genTypes => false
     25    , genTables => true
     26    , options => dbms_xmlschema.REGISTER_BINARYXML
     27    );
     28  end;
     29  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> select * from tab;
    
    TNAME                                                                                                                            TABTYPE  CLUSTERID
    -------------------------------------------------------------------------------------------------------------------------------- ------- ----------
    EMPLOYEES_XML                                                                                                                    TABLE
    
    1 row selected.
    
    SQL> 
    SQL> insert into employees_xml values(
      2   xmltype(bfilename('TEST_DIR','employees.xml'), nls_charset_id('AL32UTF8'))
      3  );
    
    1 row created.
    
    SQL> 
    SQL> commit;
    
    Commit complete.
    
    SQL> 
    SQL> select "name", dump("name")
      2       , "hiredate", dump("hiredate")
      3       , "salary", dump("salary")
      4  from employees_xml t
      5     , xmltable(
      6         '/employees/employee'
      7         passing t.object_value
      8         columns "name", "hiredate", "salary"
      9       )
     10  ;
    
    name       DUMP("NAME")                   hiredate        DUMP("HIREDATE")                                          salary DUMP("SALARY")
    ---------- ------------------------------ --------------- -------------------------------------------------- ------------- ------------------------------
    John       Typ=1 Len=4: 74,111,104,110    05-JUN-10       Typ=12 Len=7: 120,110,6,5,1,1,1                             2000 Typ=2 Len=2: 194,21
    Jack       Typ=1 Len=4: 74,97,99,107      29-FEB-12       Typ=12 Len=7: 120,112,2,29,1,1,1                            2100 Typ=2 Len=2: 194,22
    
    2 rows selected.
    Edited by: Marco Gralike on Nov 20, 2012 10:51 PM

Legend

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