HOWTO: Convert from W3C XML DateTime format to Oracle Timezone

Most XML datetime information is represented like the following:

2011-05-23T12:01:51.217+02:00

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('2011-05-23T12:01:51.217+02:00') xmlcol 
    FROM dual
 )
SELECT to_timestamp_tz(xt.datum,'YYYY-MM-DD"T"HH24:MI:SS.FF9tzh:tzm') 
       as "TO_TIMESTAMP_TZ"
FROM   datestuff 
,      xmltable('*'
                passing xmlcol
                columns
                  datum varchar2(35) PATH '/date'
                ) xt;

TO_TIMESTAMP_TZ
-----------------------------------
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).