This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Nov 16, 2012 7:34 AM by 974675 RSS

SQL Loader to upload XML file

703562 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks, I will be give it a try and see
  • 5. Re: SQL Loader to upload XML file
    703562 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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