I'm retrieving XML from our web server using UTL_HTTP and need to parse the XML and get all the IDs it is returning. If it returns one ID, EXTRACTVALUE works great. If it returns more than one, I get the error ORA-19025: EXTRACTVALUE returns value of only one node.
I get the XML, throw it in a CLOB, and then throw it into my XML_GLOBAL table -- like this
sql>desc XML_GLOBAL
Name Null? Type
----------------------------------------------------- -------- -----------
FILE_DATA SYS.XMLTYPE
V_XML_RESPONSE CLOB :=
'<response>
<requestId>1004</requestId>
<requestId>1005</requestId>
<requestId>1006</requestId>
<requestId>1007</requestId>
</response>';
INSERT INTO XML_GLOBAL
(FILE_DATA)
VALUES
(XMLTYPE(V_XML_RESPONSE));
Then I use a cursor to get the data back out of the table:
SELECT EXTRACTVALUE(FILE_DATA,'/response/requestId') request_id
FROM XML_GLOBAL;
I've tried the suggestion on this thread: , but I can't get anything to work.
I've tried a lot of other posts all over the Interweb, but can't figure it out. I just want to loop through all the requestId's and process what I need to process. Eventually, I'll have to figure out how to do this where each node has children with data. That is the next step, but I can't even get this simple task done.
I'd also like to skip the part where I have to throw it into a table. That seems silly to me, but I can't get it to work any other way.
I'm on db 11.2.0.3.0.