Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question Unanswered: Need help insert xml containing W3C datetime

    Hi all,

    I wan't to insert values from an XML file into Oracle tables. Ie.
    <tablerow col1="value" col2="value" dtcol="2007-04-30T12:53:31+02:00" />

    should insert into table:
    create table mytable
    (
    col1 varchar2(1024),
    col2 varchar2(1024),
    col3 date
    );

    I have tried to use both dbms_xmlsave and dbms_xmlstore, but neither of these know what to do with the date value contained in dtcol. Does anyone have any idea?

    Thanks,
    DayneO

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Doesn't looks like a "real" date to me ?

    Have you tried any other variant for the datatype (date with timezone, etc.) ?

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    The value "2007-04-30T12:53:31+02:00" is a datetime in the XML format defined by W3C. All apps that generate a datetime should format the date like this in the XML. It seems however that Oracle doesn't know what to do with this date in it's XML implementation.

    Not sure what to do now. They say use set_date_format on dbms_xmlsave, but then it wants a java date format which is different from an XML date format.

    Grrrr! Dates! Always seem to be a mission ....

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    characters between quote marks are NOT date datatypes; they are STRINGS!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    To make this clear:
    This is XML
    Code:
    <dataset>
       <tablerow col1="value" col2="value" dtcol="2007-04-30T12:53:31+02:00" />
       <tablerow col1="value" col2="value" dtcol="2007-04-30T12:53:31+02:00" />
       <tablerow col1="value" col2="value" dtcol="2007-04-30T12:53:31+02:00" />
    </dataset>
    This must fit into Oracle table:
    Code:
    create table mytable
    (
    col1 varchar2(1024),
    col2 varchar2(1024),
    col3 date
    );
    The dtcol attribute in the above is in format xs:datetime as defined by W3C. The format of this is yyyy-mm-ddThh:nn:sstz
    where
    yyyy = for digit year eg. 2007
    mm = 2 digit month eg. 03 (March)
    dd = 2 digit day eg. 25
    T = delimeter between date and time components
    hh = 2 digit hour eg. 23
    nn = 2 digit minute eg. 53
    ss = 2 digit seconds eg. 23
    tz = time zone eg. +02:00 (Pretoria)

    Now in a lot of other programming languages, the XML implementations convert the XML datetime values to that languages datetime equivalent without a problem, but Oracle for some reason doesn't like the format. It's as though I have to do a transformation myself. And possibly in that last sentence I have probably found the "work around".

    Thanks anyway....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •