Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

<ns3:EntryDateTime cls="U">2013-04-20T21:02:52.468-04:00</ns3:EntryDateTime>

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.

The 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.

share|improve this question

migrated from stackoverflow.com Apr 22 '13 at 8:11

This question came from our site for professional and enthusiast programmers.

    
Did you register a schema when you created the table STXP? If so, please post us the script you used to do this. –  Colin 't Hart Apr 22 '13 at 11:44

2 Answers 2

If you don't find out how to manage it with SQL Loader you could add a varchar column to table where you are importing and add an insert tigger which then converts that varchar column to some date column in the table.

share|improve this answer
    
@Colin Yes, I registered the schema containing the definition for the date string in question. In fact, this is a large project consisting of 101 schema files. I registered all of them successfully in correct sequence. This script is very large; so, I am listing only portions of it: –  Asif Apr 23 '13 at 0:12
    
@Colin Here is the script: ` DECLARE SCHEMAURL VARCHAR2( 100 ); SCHEMADOC VARCHAR2( 100 ); BEGIN SCHEMAURL := '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 ); END; /` –  Asif Apr 23 '13 at 0:36
    
Can you move these comments to the question rather than on this answer? Can you post the contents of the DataTypes.xsd file? Does it contain anything about what to do with the datetime sample? –  Colin 't Hart Apr 23 '13 at 14:52
    
@Colin'tHart I already improved my question with all these comments and answered your questions there. –  Asif Apr 23 '13 at 15:59
    
@Jokke I let Oracle break up the table data, which comes from XML files, into various columns when I load the XML file to Oracle DB. So I do not think I can set a trigger to manipulate a column value before insert. –  Asif Apr 23 '13 at 16:04

I think the way you'll have to do it is a Jokke suggest: instead of defining the DT datatype as TIMESTAMP WITH TIME ZONE, rather define it as VARCHAR2(29) where I think that 29 characters should be enough but you could define it wider.

Then when you extract data from the XML you'll have to convert it to TIMESTAMP WITH TIME ZONE.

However, you could also define the column as an "xsd:dateTime". See http://www.adp-gmbh.ch/xml/schema.html and http://www.w3.org/TR/xmlschema-2/#dateTime

share|improve this answer
    
I defined DT data type as xdb:SQLType="VARCHAR2(30)" and then tried to register it. I got this error: 'ORA-31094: incompatible SQL type "VARCHAR2(30)" for attribute or element "DT"` –  Asif Apr 24 '13 at 15:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.