1 2 Previous Next 19 Replies Latest reply: Nov 16, 2012 9:34 AM by 974675 Go to original post RSS
      • 15. Re: SQL Loader to upload XML file
        Jason_(A_Non)
        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
          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
            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)
              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
                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