How to extract multiple records from XML using PL/SQL with XE 10g
kdwolfJun 13 2011 — edited Aug 2 2011Hi 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;