1 2 Previous Next 19 Replies Latest reply: Nov 16, 2012 9:34 AM by 974675 RSS

    SQL Loader to upload XML file

    703562
      I have a a simplified xml file that looks like this

      <PARTICIPANT>
           <BIOMETRICS>
      <WGT>175</WGT>
      <HT-FT>6</HT-FT>
      <HT-IN>0</HT-IN>
      <BMI>23.7</BMI>
      <BPSYS>119</BPSYS>
      <BPDIA>68</BPDIA>
      <CHL>187</CHL>
      <FASTING>N</FASTING>
      </BIOMETRICS>
      </PARTICIPANT>
      <PARTICIPANT>
      <BIOMETRICS>
      <WGT>175</WGT>
      <HT-FT>6</HT-FT>
      <HT-IN>0</HT-IN>
      <CHL>187</CHL>
      <FASTING>N</FASTING>
      </BIOMETRICS>
      </PARTICIPANT>

      I wrote a control file that looks like this

      load data
      infile 'test.xml' "str '</PARTICIPANT>'"
      truncate
      into table test_xml
      (
      PARTICIPANT filler terminated by ">",
      BIOMETRICS filler terminated by ">",
      WGT enclosed by "<WGT>" and "</WGT>",
      HT_FT enclosed by "<HT-FT>" and "</HT-FT>",
      HT_IN enclosed by "<HT-IN>" and "</HT-IN>",
      BMI enclosed by "<BMI>" and "</BMI>",
      BPSYS enclosed by "<BPSYS>" and "</BPSYS>",
      BPDIA enclosed by "<BPDIA>" and "</BPDIA>",
      CHL enclosed by "<CHL>" and "</CHL>",
      FASTING enclosed by "<FASTING>" and "</FASTING>",
      CLOSEBIOMETRICS filler terminated by ">"
      )

      Error message reads

      Record 2: Rejected - Error on table TEST_XML, column BMI.
      Initial enclosure character not found

      It errors on record 2 as BMI is not present in the second record. What I want to know is how do I make some fields optional? ie if they are not present, set that variable to null or do nothing.
      Thanks
      Lalitha

      Edited by: user10151006 on Oct 6, 2010 1:41 PM

      Edited by: user10151006 on Oct 6, 2010 1:42 PM

      Edited by: user10151006 on Oct 6, 2010 2:22 PM
        • 1. Re: SQL Loader to upload XML file
          Marco Gralike
          Have a look at the control files used here: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb25loa.htm#BGBDDEDD
          • 2. Re: SQL Loader to upload XML file
            703562
            This is not easy to understand - I have already looked at it - I wish they would give examples

            Edited by: user10151006 on Oct 7, 2010 11:25 AM
            • 3. Re: SQL Loader to upload XML file
              Jason_(A_Non)
              That is the example.

              They aren't doing it in the manner you are trying to do because SQL*Loader was not, is not, and will not be an XML parser.

              What the example does is first register a schema into the DB that the XML is based upon. This is the DBMS_XMLSCHEMA.registerschema step. There is a utility provided by Oracle to help prepare the schema for registering in the DB. This can be found at http://www.oracle.com/technetwork/database/features/xmldb/index.html under the Oracle XML DB Utility Scripts link.

              Then the example creates a table based the schema that was just registered. This means the table can only accept XML that is valid per the schema.

              The example finally shows some control file examples for loading the XML as a whole into that table.

              The parsing of the XML that you are expected would be handled via SQL SELECT using XMLTable or XQuery when reading data from the table. This is where optional columns would easily be handled. They would look just like your normal empty column when doing a SELECT statement.

              Could you do less of these steps? Yes, meaning you could avoid registering the schema and just create an XMLType table that accepts any XML. Would this impact performance. Probably but the degree depends upon your version of Oracle.

              Edited by: A_Non on Oct 7, 2010 1:40 PM
              Added link.
              Also, include your version (4 digits) for better specific help.
              • 4. Re: SQL Loader to upload XML file
                703562
                Thanks, I will be give it a try and see
                • 5. Re: SQL Loader to upload XML file
                  703562
                  I wish the example had used more complicated xml like nested xml

                  Edited by: user10151006 on Oct 8, 2010 11:08 AM

                  Edited by: user10151006 on Oct 8, 2010 11:09 AM
                  • 6. Re: SQL Loader to upload XML file
                    703562
                    I don't have permission to create schemas. So I can I use any schema to create a table of xmltype?
                    • 7. Re: SQL Loader to upload XML file
                      703562
                      I know we have 11g and I created a table of type xmltype. Now how do I load the xml file into this table? Thanks

                      Edited by: user10151006 on Oct 12, 2010 11:41 AM
                      • 8. Re: SQL Loader to upload XML file
                        703562
                        BEGIN
                        DBMS_XMLSCHEMA.registerschema('http://www.oracle.com/participant.xsd',
                        '<schema xmlns="http://www.w3.org/2001/XMLSchema"' ||
                        ' xmlns:per="http://www.oracle.com/participant.xsd"' ||
                        ' xmlns:xdb="http://xmlns.oracle.com/xdb"' ||
                        ' elementFormDefault="qualified"' ||
                        ' targetNamespace="http://www.oracle.com/participant.xsd">' ||
                        ' <element name="participant" type="per:participanttype"' ||
                        ' xdb:SQLType="participant_T"/>' ||
                        ' <complexType name="participanttype" xdb:SQLType="participant_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;

                        gives the following error :


                        Error starting at line 1 in command:
                        BEGIN
                        DBMS_XMLSCHEMA.registerschema('http://www.oracle.com/participant.xsd',
                        '<schema xmlns="http://www.w3.org/2001/XMLSchema"' ||
                        ' xmlns:per="http://www.oracle.com/participant.xsd"' ||
                        ' xmlns:xdb="http://xmlns.oracle.com/xdb"' ||
                        ' elementFormDefault="qualified"' ||
                        ' targetNamespace="http://www.oracle.com/participant.xsd">' ||
                        ' <element name="participant" type="per:participanttype"' ||
                        ' xdb:SQLType="participant_T"/>' ||
                        ' <complexType name="participanttype" xdb:SQLType="participant_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-31094: incompatible SQL type "participant_T" for attribute or element "participant"
                        ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 3
                        ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 14
                        ORA-06512: at line 2
                        31094. 00000 - "incompatible SQL type \"%s\" for attribute or element \"%s\""
                        *Cause:    The SQL type information provided in the XML schema is not
                        compatible with the XML datatype for the specified attribute
                        or element.
                        *Action:   Make sure that the specified SQL types are compatible with the
                        declared XML datatypes.


                        Also I accidentally created a table called Participant using

                        CREATE TABLE participant OF XMLType
                        XMLSCHEMA "http://www.oracle.com/participant.xsd" ELEMENT "participant";

                        which I am unable to drop with "Drop table participant" SQL command.
                        I am now thoroughly confused!

                        Edited by: user10151006 on Oct 12, 2010 1:16 PM
                        • 9. Re: SQL Loader to upload XML file
                          Jason_(A_Non)
                          So I can I use any schema to create a table of xmltype?
                          Yes. Registering a schema just provides Oracle with additional details about the XML that is stored and so lets Oracle validate the XML before it is stored in the DB and can also be used to improve performance when querying the data
                          I know we have 11g
                          Right now I know of 4 versions of 11g. 11.1.0.6, 11.1.0.7, 11.2.0.2 and 11.2.0.2. A lot has changed from the first to the last, hence we we ask for all 4 digits. Connecting via SQL Plus alone will tell you the 4 digits
                          Connected to:
                          Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options
                          
                          SQL>
                          You would use sqlldr just like you were trying to do before, only this time your control file is setup to load the XML as one object instead of trying to parse it. So like the example shows it would just be
                          sqlldr load_data.ctl
                          but using whatever your control file is named.
                          • 10. Re: SQL Loader to upload XML file
                            703562
                            The oracle version is 11.1.0.7.0
                            and now I have persmission to create schemas and I would like to parse the xml as I upload - so that part is clear. However I am having trouble registering the schema and have listed the PL/SQL and error message in another thread. Thanks for all your help.
                            • 11. Re: SQL Loader to upload XML file
                              Jason_(A_Non)
                              I don't see your other post and this would be the forum to post that information in. Just so you know.
                              • 12. Re: SQL Loader to upload XML file
                                703562
                                Nope it looks like I am unable to create the schema - so I am taking the easy route.

                                create table testxml of MMLType;

                                I created a test.ctrl that looks like this

                                LOAD DATA
                                INFILE 'H:\testsmall.xml'
                                INTO TABLE testxml TRUNCATE
                                XMLType(xmldata)
                                FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                                (
                                xmldata
                                )

                                My testsmall.xml file looks like this

                                <PARTICIPANT>
                                <UNIQUEID>123456789</UNIQUEID>
                                <SCANDATE>2010-01-04T14:04:54</SCANDATE>
                                <DEMOGRAPHICS>
                                <FIRSTNAME>Lalitha</FIRSTNAME>
                                <LASTNAME>Natarajan</LASTNAME>
                                <EMAIL />
                                </DEMOGRAPHICS>
                                <BIOMETRICS>
                                <WGT>175</WGT>
                                <HT-FT>6</HT-FT>
                                <HT-IN>0</HT-IN>
                                </BIOMETRICS>
                                blah blah
                                </PARTICIPANT>

                                I ran sqlldr test.ctrl ERRORS=1000000000

                                the table loaded but it stopped loading in the middle of a <PARTICIPANT> </PARTICIPANT> (the xml file is fine)
                                with the following errors in the log file

                                Record 1: Rejected - Error on table TESTXML.
                                ORA-31011: XML parsing failed
                                ORA-19202: Error occurred in XML processing
                                LPX-00007: unexpected end-of-file encountered
                                ORA-06512: at "SYS.XMLTYPE", line 5

                                Record 4: Rejected - Error on table TESTXML.
                                ORA-31011: XML parsing failed
                                ORA-19202: Error occurred in XML processing
                                LPX-00007: unexpected end-of-file encountered
                                ORA-06512: at "SYS.XMLTYPE", line 5

                                Record 19: Rejected - Error on table TESTXML.
                                ORA-31011: XML parsing failed
                                ORA-19202: Error occurred in XML processing
                                LPX-00231: invalid character 47 ('/') found in a Name or Nmtoken
                                Error at line 1
                                ORA-06512: at "SYS.XMLTYPE", line 5

                                I am stumped!!
                                • 13. Re: SQL Loader to upload XML file
                                  703562
                                  I posted it and then deleted it thinking I can't create schemas. I'll post it again tomorrow - I do want my xml to be parsed - will try it again - Thanks for all your help.

                                  Edited by: user10151006 on Oct 12, 2010 3:13 PM
                                  • 14. Re: SQL Loader to upload XML file
                                    703562
                                    Alright my xml file looks as follows:

                                    <PARTICIPANT>
                                    <UNIQUEID>123456789</UNIQUEID>
                                    <SCANDATE>2010-01-04T14:04:54</SCANDATE>
                                    <BIOMETRICS>
                                    <WGT>175</WGT>
                                    <HT-FT>6</HT-FT>
                                    <HT-IN>0</HT-IN>
                                    <BMI>23.7</BMI>
                                    <BPSYS>119</BPSYS>
                                    <BPDIA>68</BPDIA>
                                    <CHL>187</CHL>
                                    <FASTING>N</FASTING>
                                    </BIOMETRICS>
                                    <PARTICIPANTSCOREDETAIL>
                                    <TOP3RISKS>
                                    <RISK1>EAT</RISK1>
                                    <RISK2>STR</RISK2>
                                    <RISK3>WGT</RISK3>
                                    </TOP3RISKS>
                                    </PARTICIPANTSCOREDETAIL>
                                    </PARTICIPANT>


                                    I created a type

                                    CREATE TYPE lalitha_t AS OBJECT(
                                    UNIQUEID     VARCHAR2(50),
                                    SCANDATE     VARCHAR2(50),
                                    BIOMETRICS VARCHAR2(500),
                                    RISK1     VARCHAR2(50),
                                    RISK2     VARCHAR2(50),
                                    RISK3     VARCHAR2(50)
                                    );

                                    which ran succesfully

                                    Then I ran the following script

                                    BEGIN
                                    -- Delete schema if it already exists (else error)
                                    DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/lalitha.xsd', 4);
                                    END;

                                    which gave the following error:

                                    Error starting at line 1 in command:
                                    BEGIN
                                    -- Delete schema if it already exists (else error)
                                    DBMS_XMLSCHEMA.deleteSchema('http://www.oracle.com/lalitha.xsd', 4);
                                    END;
                                    Error report:
                                    ORA-31000: Resource 'http://www.oracle.com/lalitha.xsd' is not an XDB schema document
                                    ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 106
                                    ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 102
                                    ORA-06512: at line 3
                                    31000. 00000 - "Resource '%s' is not an XDB schema document"
                                    *Cause:    The given schema URL does not refer to a registered XDB schema
                                    *Action:   Make sure the specified schema has been registered in XDB

                                    Which I thought was because this scema had not been created ...

                                    Then I ran the following script:

                                    BEGIN
                                    DBMS_XMLSCHEMA.registerschema('http://www.oracle.com/lalitha.xsd',
                                    '<schema xmlns="http://www.w3.org/2001/XMLSchema"' ||
                                    ' xmlns:per="http://www.oracle.com/lalitha.xsd"' ||
                                    ' xmlns:xdb="http://xmlns.oracle.com/xdb"' ||
                                    ' elementFormDefault="qualified"' ||
                                    ' targetNamespace="http://www.oracle.com/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;

                                    and it gives the following error:


                                    Error starting at line 1 in command:
                                    BEGIN
                                    DBMS_XMLSCHEMA.registerschema('http://www.oracle.com/lalitha.xsd',
                                    '<schema xmlns="http://www.w3.org/2001/XMLSchema"' ||
                                    ' xmlns:per="http://www.oracle.com/lalitha.xsd"' ||
                                    ' xmlns:xdb="http://xmlns.oracle.com/xdb"' ||
                                    ' elementFormDefault="qualified"' ||
                                    ' targetNamespace="http://www.oracle.com/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-31094: incompatible SQL type "lalitha_T" for attribute or element "lalitha"
                                    ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 3
                                    ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 14
                                    ORA-06512: at line 2
                                    31094. 00000 - "incompatible SQL type \"%s\" for attribute or element \"%s\""
                                    *Cause:    The SQL type information provided in the XML schema is not
                                    compatible with the XML datatype for the specified attribute
                                    or element.
                                    *Action:   Make sure that the specified SQL types are compatible with the
                                    declared XML datatypes.
                                    1 2 Previous Next