ORACLE parsing XML flles
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 capsFound: 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'---------------------