SQL & PL/SQL

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.