I am using Oracle 11g XML database and trying to load XML documents to this DB with a control file and the
sqlldr utility. All these XML files have an element that contains a date string with time stamp (and the letter T in the middle of it). Oracle rejects this date string because of T in it and thus the XML file is not loaded to the DB.
I want to use the Oracle function
TO_TIMESTAMP_TZ on the date string during the data load, but I do not know how to do it. That's where I need help. If there is any other way to import the XML (with date string with timestamp), I will try that also.
Here is the date entry in XML file:
And here is entire code the control file:
load data infile 'filelist.dat' append into table STXP xmltype(XMLDATA) ( filename filler char(120), XMLDATA lobfile(filename) terminated by eof )
I believe that I can execute the above control file with the
sqlldr utility on SQL*Plus command line also, but not sure about this option. If this is possible, I guess I can ALTER SESSION (to somehow format date string) on command line before executing the control file.
filelist.dat mentioned above contains entries for input XML file, with one line listing one XML file. The above date entry is required in each XML file. Each XML file has about 50 different elements, some required and some optional. I would greatly appreciate your help.
UPDATE: I successfully registered the schema, which contains definition for the date string, and 100 other schema, with a script. Since this script is very large, I am posting only 2 registration portions of it:
DECLARE SCHEMAURL VARCHAR2( 100 ); SCHEMADOC VARCHAR2( 100 ); BEGIN SCHEMAURL := 'http://www.some.org/stxp/DataTypes.xsd'; SCHEMADOC := 'DataTypes.xsd'; DBMS_XMLSCHEMA.registerSchema( SCHEMAURL, BFILENAME( 'XSD_DIR', SCHEMADOC ), LOCAL => TRUE, -- local GENTYPES => TRUE, -- generate object types GENBEAN => FALSE, -- no java beans GENTABLES => TRUE, -- generate object tables OWNER => USER ); SCHEMAURL := 'http://www.some.org/stxp/STXP.xsd'; SCHEMADOC := 'STXP.xsd'; DBMS_XMLSCHEMA.registerSchema( SCHEMAURL, BFILENAME( 'XSD_DIR', SCHEMADOC ), LOCAL => TRUE, -- local GENTYPES => TRUE, -- generate object types GENBEAN => FALSE, -- no java beans GENTABLES => TRUE, -- generate object tables OWNER => USER ); END; /
The 2nd registration above is the last in the script, and this creates the table STXP, in which I am trying to load about 800 XML files. Each XML file has a root element called stxp.
This is the relevant definition of date string:
<xsd:simpleType name="DT" xdb:SQLType="TIMESTAMP WITH TIME ZONE"> <xsd:restriction base="xsd:dateTime"/> </xsd:simpleType>
And this is how I am using the above definition:
<element name="EntryDateTime" type="oth:DT"/>
When I make the above element optional (for testing purpose) and remove the date string entry (mentioned near the top of this question) from my XML file, the XML file is loaded successfully to Oracle XML database. When I put this entry back to XML file (because it is required), Oracle rejects it.
Because I let Oracle take care of population of STXP table with data from XML files, I am not sure if I can set a trigger to pre-process the date string from the input XML file before saving it in database. i think there is a way to do it in the control file.