Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to extract multiple records from XML using PL/SQL with XE 10g

kdwolfJun 13 2011 — edited Aug 2 2011
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;
This post has been answered by odie_63 on Jun 14 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 30 2011
Added on Jun 13 2011
8 comments
11,969 views