HOWTO: Convert from W3C XML DateTime format to Oracle Timezone

Most XML datetime information is represented like the following:


So said that, how do you get from that format to a Oracle datetype… The following will help converting you from the W3C datetime towards Oracle datetypes…

WITH datestuff AS
 (SELECT xmltype('<date>2011-05-23T12:01:51.217+02:00</date>') xmlcol 
    FROM dual
SELECT to_timestamp_tz(xt.datum,'YYYY-MM-DD"T"HH24:MI:SS.FF9tzh:tzm') 
FROM   datestuff 
,      xmltable('*'
                passing xmlcol
                  datum varchar2(35) PATH '/date'
                ) xt;
23-05-11 12:01:51,217000000 +02:00

So use the timestamp with timezone function and make sure the format fits while using a varchar2(35).