PL/SQL. Cannot extract values from xmltable

Question

I am trying to send a clob and then destructure it like:

This is how I am sending the message:

declare
  messageClob clob;
  log_id number;
begin
  log_id := 12; --for testing
  messageClob := to_clob(
  '
      <myReq xmlns="http://xxx/xx/x" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://xxx/xx/x file:/C:/Users/User1/Documents/test.xsd">
         <signature>HelloWorld</signature>
         <status>End</status>
         <mydate>2005-03-29T13:24:36.000</mydate>
      </myReq >
  ');
  my_pkg.getMyReq(p_messageClob => messageClob,
                  p_logId       => log_id);
end;

And this is the code to desctructure it:

  procedure getMyReq(p_messageClob clob, p_logId number) is
    logHistoryId number;
  begin

    if p_messageClob is not null then
      dbms_output. put_line(p_messageClob);

      for message In (select * from xmltable(
                        xmlnamespaces(default 'http://xxx/xx/x'),
                        'myReq' passing xmltype(p_messageClob)
                        columns
                          signature varchar2(50) path 'signature',
                          status varchar2(10) path 'status',
                          mydate date path 'mydate')) 
      loop
        dbms_output.put_line(message.signature || ', ' || message.status|| ', ' || message.mydate );
       
      end loop;
    end if;
  exception
    ...some code
  end getMyReq;

What am I missing here? This is the same scenario as always and still there is noting printed. I am sure that message is not null because dbms_output. put_line(p_messageClob); prints the clob.

Answer 1

The problem is this:

mydate date path 'mydate'

(and your procedure squashing the error). It is effectively doing to_date('2005-03-29T13:24:36.000', 'YYYY-MM-DD') which throws the same error.

You can either retrieve it as a string:

mydate varchar2(23) path 'mydate'

and convert it later, or retrieve it as a timestamp, which even handles the ISO-8601 'T' separator unlike Oracle's timestamp literal:

mydate timestamp path 'mydate'

db<>fiddle