SQL & PL/SQL

I'm trying to parse the XML in an RSS feed to get some information. I'm able to do it perfectly for 1 of the RSS links, however, with the other one, I cannot seem to get the information. Here's the link that the code currently works for:

http://www.simplyhired.com/a/job-feed/rss/sq-atlanta%2C+ga/o-35/fft-cook

And here's the RSS link that I'm trying to adapt my code to work for:

http://auburn.craigslist.org/fbh/index.rss

And here's my pl/sql code:

LOOP
  utl_http.set_proxy(apex_application.g_proxy_server, NULL);
  l_http_req := utl_http.begin_request(i.LINK);
  l_http_resp := utl_http.get_response(l_http_req);
  dbms_lob.createtemporary( l_clob, FALSE );
  dbms_lob.open( l_clob, dbms_lob.lob_readwrite );
  begin
    loop
      utl_http.read_text(l_http_resp, l_buffer);
      dbms_lob.writeappend( l_clob, length(l_buffer), l_buffer );
    end loop;
    exception
     when others then
      if sqlcode <> -29266 then
       raise;
      end if;
  end;
  --
  utl_http.end_response(l_http_resp);
 
  apex_collection.add_member(
    p_collection_name => 'RSS_RESPONSE',
    p_clob001         => l_clob );

  apex_collection.add_member(
    p_collection_name => 'RSS_RESPONSE_TOTAL',
    p_clob001         => l_clob );


  -- Extracting values from the collection and storing
  -- the results in a table.
  FOR x in (
  SELECT extractValue(value(t),'/*/title') Title,
         extractValue(value(t),'/*/link') Link,
         extractValue(value(t),'/*/pubDate') PublishDate,
         extractValue(value(t),'/*/description') Description
  FROM apex_collections c, 
       table(xmlsequence(extract(xmltype.createxml(c.clob001),'/rss/channel/item'))) t 
  WHERE c.collection_name = 'RSS_RESPONSE'
  )
  LOOP

    SELECT count(1) into counter_var
    FROM JOB_RSS_FEEDS_CONTENT
    WHERE LINK = x.Link;
	
    if (counter_var > 0) then
      counter_var := 0;
    else
     
      SELECT JOB_RSS_FEEDS_SEQ.NEXTVAL INTO link_counter FROM DUAL;
  
      INSERT INTO JOB_RSS_FEEDS_CONTENT(ID, TITLE, LINK, DESCRIPTION, JOBTYPE, PUB_DATE, METROAREA, STATE, ZIPCODE, LINK_SOURCE)
      VALUES (link_counter, x.Title, x.Link, x.Description, i.POSITION, to_date(substr(UPPER(x.PublishDate), 1, length(x.PublishDate)-3), 'DY, DD MON YYYY HH24:MI:SS'), initcap(i.METROAREA), upper(i.STATE), i.ZIPCODE, i.LINK_SOURCE);

    end if;
  END LOOP;


  --resetting our collection to be used for the next rss feed
  apex_collection.create_or_truncate_collection('RSS_RESPONSE');
  l_clob := NULL;
  l_buffer := NULL;
  
  
  --
  -- If no records left to process or if we've cycled through
  -- links_to_read_in iterations, then exit loop.
  --
  if ((tmp_count = 0) or (loop_count >= (links_to_read_in-1))) then
    exit;
  end if;
 

  -- incrementing our iteration counter
  loop_count := loop_count + 1;

END LOOP;  -- for SELECT LINK, METROAREA, STATE, ZIPCODE loop

I figured the issue is with the line:

table(xmlsequence(extract(xmltype.createxml(c.clob001),'/rss/channel/item'))) t 

I tried changing it to:

table(xmlsequence(extract(xmltype.createxml(c.clob001),'/'))) t 

And then to:

table(xmlsequence(extract(xmltype.createxml(c.clob001),'/rdf/item'))) t

But those didn't work either. Can't think of what else it might be. Any thoughts?

Edited by: taneal on Oct 27, 2008 12:05 PM