This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Nov 16, 2012 7:34 AM by 974675 Go to original post RSS
  • 15. Re: SQL Loader to upload XML file
    Jason_(A_Non) Expert
    Currently Being Moderated
    The short answer is in your schema change "lalitha_t" to "LALITHA_T". It is case sensitive.

    Backing up a bit, is object relational storage the best for you in your situation? Oracle provides a document called Oracle XML DB : Choosing the Best XMLType Storage Option for Your Use Case at http://www.oracle.com/technetwork/database/features/xmldb/index.html.

    Don't use www.oracle.com in the URIs unless you work for Oracle. Make up something appropriate based on your company/project/etc.

    Your XML doesn't match the schema in terms of namespaces nor nodes defined. If you are going to provide trimmed down examples for the forums, please ensure they match up. Also look in the FAQ in the upper right for how to use the
     tag to wrap code to retain formatting.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 16. Re: SQL Loader to upload XML file
    703562 Newbie
    Currently Being Moderated
    I haven't trimmed down anything in my latest post ...
    Thasnk for the upper case tip
    and the www.oracle tip ... :)
  • 17. Re: SQL Loader to upload XML file
    703562 Newbie
    Currently Being Moderated
    I am getting the following error when I run this ...

    Error starting at line 1 in command:
    BEGIN
    DBMS_XMLSCHEMA.registerschema('lalitha.xsd',
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"' ||
    ' xmlns:per="lalitha.xsd"' ||
    ' xmlns:xdb="xdb"' ||
    ' elementFormDefault="qualified"' ||
    ' targetNamespace="lalitha.xsd">' ||
    ' <element name="lalitha" type="per:lalithatype"' ||
    ' xdb:SQLType="LALITHA_T"/>' ||
    ' <complexType name="lalithatype" xdb:SQLType="LALITHA_T">' ||
    ' <sequence>' ||
    ' <element name="uniqueid" type="string" xdb:SQLName="uniqueid"' ||
    ' xdb:SQLType="VARCHAR2"/>' ||
    ' <element name="scandate" type="string" xdb:SQLName="scandate"' ||
    ' xdb:SQLType="VARCHAR2"/>' ||
    ' <element name="biometrics" type="string" xdb:SQLName="biometrics"' ||
    ' xdb:SQLType="VARCHAR2"/>' ||
    ' <element name="risk1" type="string" xdb:SQLName="risk1"' ||
    ' xdb:SQLType="VARCHAR2"/>' ||
    ' <element name="risk2" type="string" xdb:SQLName="risk2"' ||
    ' xdb:SQLType="VARCHAR2"/>' ||
    ' <element name="risk3" type="string" xdb:SQLName="risk3"' ||
    ' xdb:SQLType="VARCHAR2"/>' ||
    ' </sequence>' ||
    ' </complexType>' ||
    ' </schema>',
    TRUE,
    FALSE,
    FALSE);
    END;
    Error report:
    ORA-20001: enable_hierarchy_internal
    ORA-06512: at "XDB.DBMS_XDBZ", line 28
    ORA-06512: at line 1
    ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 3
    ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 14
    ORA-06512: at line 2
  • 18. Re: SQL Loader to upload XML file
    Jason_(A_Non) Expert
    Currently Being Moderated
    Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
     
    SQL> 
    SQL> CREATE TYPE lalitha_t AS OBJECT(
      2  UNIQUEID VARCHAR2(50),
      3  SCANDATE VARCHAR2(50),
      4  BIOMETRICS VARCHAR2(500),
      5  RISK1 VARCHAR2(50),
      6  RISK2 VARCHAR2(50),
      7  RISK3 VARCHAR2(50)
      8  );
      9  /
     
    Type created
     
    SQL> 
    SQL> DECLARE
      2     l_schema   CLOB;
      3  BEGIN
      4     l_schema := '<schema xmlns="http://www.w3.org/2001/XMLSchema"
      5   xmlns:per="lalitha.xsd"
      6   xmlns:xdb="xdb"
      7   elementFormDefault="qualified"
      8   targetNamespace="lalitha.xsd">
      9   <element name="lalitha" type="per:lalithatype"
     10   xdb:SQLType="LALITHA_T"/>
     11   <complexType name="lalithatype" xdb:SQLType="LALITHA_T">
     12   <sequence>
     13   <element name="uniqueid" type="string" xdb:SQLName="uniqueid"
     14   xdb:SQLType="VARCHAR2"/>
     15   <element name="scandate" type="string" xdb:SQLName="scandate"
     16   xdb:SQLType="VARCHAR2"/>
     17   <element name="biometrics" type="string" xdb:SQLName="biometrics"
     18   xdb:SQLType="VARCHAR2"/>
     19   <element name="risk1" type="string" xdb:SQLName="risk1"
     20   xdb:SQLType="VARCHAR2"/>
     21   <element name="risk2" type="string" xdb:SQLName="risk2"
     22   xdb:SQLType="VARCHAR2"/>
     23   <element name="risk3" type="string" xdb:SQLName="risk3"
     24   xdb:SQLType="VARCHAR2"/>
     25   </sequence>
     26   </complexType>
     27   </schema>';
     28     DBMS_XMLSCHEMA.registerschema('lalitha.xsd',
     29                                   l_schema,
     30                                   TRUE,
     31                                   FALSE,
     32                                   FALSE);
     33  END;
     34  /
     
    PL/SQL procedure successfully completed
    So it works for me. Have a look at the answers from mdrake (Mark) in {thread:id=834377} and see if you are doing any of those.
  • 19. Re: SQL Loader to upload XML file
    974675 Newbie
    Currently Being Moderated
    So next should we prepare the control file and run it?

    LOAD DATA
    INFILE filename.xml
    INTO TABLE foo TRUNCATE
    XMLType(xmldata)
    FIELDS(fill filler CHAR(1),
    xmldata LOBFILE (CONSTANT filename.xml)
    TERMINATED BY EOF)


    Is it right?
1 2 Previous Next

Legend

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