DevOps, CI/CD and Automation

Hi all,

I need your advise, please.
I have a CLOB object as a result of a Web Service, which I need to parse. I use XE version, so XQuery is not for me, as I will get SYS.DBMS_XQUERYINT exception.

I have tried to use extract, but looks I am doing something wrong there. Please point me to what should I change?

(1) I have a temp table with id and xmltype
(2) I have tested that the data from the example below is in the table
(3) I receive nothing no matter if I use ,'/modifyPlannedItemStatus/days[1]/day_of_week_order' or just ,'/modifyPlannedItemStatus/days[1]/day_of_week_order'.

Thanks in advance


DECLARE
my_xml XMLTYPE ;
my_clob CLOB :='<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<mgns1:modifyPlannedItemStatus xmlns:mgns1="http://www.mart-sw.co.uk/library">
<days>
<day_of_week_code>A</day_of_week_code>
<day_of_week_order>1</day_of_week_order>
<day_of_week_name>Monday</day_of_week_name>
</days>
<days>
<day_of_week_code>B</day_of_week_code>
<day_of_week_order>2</day_of_week_order>
<day_of_week_name>Tuesday</day_of_week_name>
</days>
</mgns1:modifyPlannedItemStatus>';

i INTEGER :=1;

my_varchar VARCHAR2(3000);
BEGIN

insert into session_temp_xml values (i, xmltype.createxml(my_clob));

SELECT to_clob(extract(s.xml_data,'/modifyPlannedItemStatus/days[1]/day_of_week_order')) INTO my_varchar FROM session_temp_xml s;


dbms_output.put_line('My result '||rtrim(my_varchar));


END;