ORACLE parsing XML flles

Question

In one of my procedure I am parsing a remote stored XML file using a REST call (in APEX) and trying to find out nodes that contain specific terms.

Here's a simplified example structure of the file. The search term in this example is 'cloud':

 <map id="12343">
      <topic id="23498">
        <title>Topic title</title>
        <p id="24334"> some sample text with term 'cloud' </p>
        <ul id = "34334">
          <li id="38743">List item without the term </li>
          <li id="38438">List item with term 'Cloud'</li>
        </ul>
      </topic>
      <topic id="23498">
        <title>Title for this topic</title>
        <p id="24334"> some sample text with term 'cloud' </p>
        <ul id = "34334">
          <li id="38743">List item without the term </li>
          <li id="38438">List item without term'</li>
        </ul>
      </topic>
      <topic id="23498">
        <title>Title for this topic with term 'CLOUD' in caps</title>
        <p id="24334"> some sample text with term 'Cloud' </p>
        <ul id = "34334">
          <li id="38743">List item without the term </li>
          <li id="38438">List item without term'</li>
        </ul>
      </topic>
    </map>

The code is expected to parse this file and find out IDs of the node that contains the term 'cloud' anywhere in the text inside that node.

I am using existnode to find this out, but I am not getting correct results:

declare
sourceXML clob;
begin
delete from result_table;
for f in (select file_id, files_path from my_table)
  loop
  /*Get the contents of the file in the sourceXML*/
    sourceXML := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url => f.file_path,
    p_http_method => 'GET');

    if instr(sourceXML,'<?xml version') != 0 then /* verify if it's valid xml file */
      for t in (select topic_id
                      FROM xmltable('//map/topic' passing XMLTYPE(sourceXML)
                          columns topic_id VARCHAR2(10) PATH './@id')
                      where XMLExists('//text()[ora:contains(.,"sales cloud")]' passing XMLTYPE(sourceXML)))
      loop
         insert into result_table (file,topic) values (f.file_id, t.topic_id);
      end loop;
    end if;
  end loop;
end;

I am not able to figure out where I am going wrong.

Answer 1

I separated simple tags from list tags and search each one in two loops:

DECLARE
   V_XML         VARCHAR2 (4096) := '<map id="12343">
<topic id="23498">
<title>Topic title</title>
<p id="24334"> some sample text with term ''cloud''</p>
<ul id = "34334">
<li id="38743">List item without the term</li>
<li id="38438">List item with term ''Cloud''</li>
</ul>
</topic>
<topic id="23498">
<title>Title for this topic</title>
<p id="24334"> some sample text with term ''cloud''</p>
<ul id = "34334">
<li id="38743">List item without the term</li>
<li id="38438">List item without term''</li>
</ul>
</topic>
<topic id="23498">
<title>Title for this topic with term ''CLOUD'' in caps</title>
<p id="24334"> some sample text with term ''Cloud''</p>
<ul id = "34334">
<li id="38743">List item without the term</li>
<li id="38438">List item without term''</li>
</ul>
</topic>
</map>';
   V_XML_CHILD   VARCHAR2 (4096);
   V_TEXT        VARCHAR2 (4096);
   V_ID          VARCHAR2 (4096);
   V_NAME        VARCHAR2 (4096);
   V_PARENT_ID   VARCHAR2 (4096);
   V_CNT         NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE (
      '-------Looking in simple tags for each topic--------------');

   FOR REC IN (SELECT COLUMN_VALUE VAL
               FROM XMLTABLE (
                              '//map/topic'
                              PASSING XMLTYPE (V_XML)
                             ))
   LOOP
      V_CNT := 0;
      V_XML_CHILD := REC.VAL.GETSTRINGVAL ();

      SELECT TAG_ID
      INTO V_PARENT_ID
      FROM XMLTABLE (
              '*'
              PASSING XMLTYPE (V_XML_CHILD)
              COLUMNS TAG_NAME VARCHAR2 (100) PATH 'name()',
                      TAG_ID VARCHAR2 (100) PATH '@id');

      FOR R_LINE
         IN (SELECT TAG_NAME, TAG_ID, TAG_VALUE
             FROM XMLTABLE (
                     'topic/*'
                     PASSING XMLTYPE (V_XML_CHILD)
                     COLUMNS TAG_NAME VARCHAR2 (100) PATH 'name()',
                             TAG_VALUE VARCHAR2 (100) PATH 'text()',
                             TAG_ID VARCHAR2 (100) PATH '@id'))
      LOOP
         V_CNT := V_CNT + 1;
         V_ID := NVL (R_LINE.TAG_ID, V_PARENT_ID);--nvl here 
         V_NAME := R_LINE.TAG_NAME;               
         V_TEXT := R_LINE.TAG_VALUE;              

         --DBMS_OUTPUT.PUT_LINE (V_CNT || '- id['||V_ID||'] - Name['||V_NAME||'] Text:' || V_TEXT);

         IF V_ID <> 'ul' AND INSTR (UPPER (V_TEXT), 'CLOUD') > 1
         THEN
            DBMS_OUTPUT.PUT_LINE (
                  'Found: Tag Id['
               || V_ID
               || '] - Tag Name['
               || V_NAME
               || '] Text:'
               || V_TEXT);
         END IF;
      END LOOP;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('---------------------');
   DBMS_OUTPUT.PUT_LINE (
      '-------Looking in list tags for each topic--------------');

   FOR REC
      IN (SELECT CHILDS VAL
          FROM XMLTABLE (
                         '//map/topic'
                         PASSING XMLTYPE (V_XML)
                         COLUMNS CHILDS XMLTYPE PATH 'ul'
                        ))
   LOOP
      V_CNT := 0;

      FOR LINE
         IN (SELECT *
             FROM XMLTABLE (
                     'ul/*'
                     PASSING XMLTYPE (REC.VAL.GETSTRINGVAL ())
                     COLUMNS TAG_NAME VARCHAR2 (100) PATH 'name()',
                             TAG_VALUE VARCHAR2 (100) PATH 'text()',
                             TAG_ID VARCHAR2 (100) PATH '@id'))
      LOOP
         V_CNT := V_CNT + 1;
         V_ID := LINE.TAG_ID;                     
         V_NAME := LINE.TAG_NAME;                 
         V_TEXT := LINE.TAG_VALUE;                

         --DBMS_OUTPUT.PUT_LINE (V_CNT || '- id['||V_ID||'] - Name['||V_NAME||'] Text:' || V_TEXT);

         IF V_ID <> 'ul' AND INSTR (UPPER (V_TEXT), 'CLOUD') > 1
         THEN
            DBMS_OUTPUT.PUT_LINE (
                  'Found: Tag Id['
               || V_ID
               || '] - Tag Name['
               || V_NAME
               || '] Text:'
               || V_TEXT);
         END IF;
      END LOOP;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('---------------------');
END;

the out put is:

-------Looking in simple tags for each topic--------------
Found: Tag Id[24334] - Tag Name[p] Text: some sample text with term 'cloud'
Found: Tag Id[24334] - Tag Name[p] Text: some sample text with term 'cloud'
Found: Tag Id[23498] - Tag Name[title] Text:Title for this topic with term 'CLOUD' in caps
Found: Tag Id[24334] - Tag Name[p] Text: some sample text with term 'Cloud'
---------------------
-------Looking in list tags for each topic--------------
Found: Tag Id[38438] - Tag Name[li] Text:List item with term 'Cloud'
---------------------