ORACLE parsing XML flles

Question

在我的一个过程中，我正在使用 REST 调用(在 APEX 中(解析远程存储的 XML 文件，并尝试找出包含特定术语的节点。

下面是该文件的简化示例结构。此示例中的搜索词为"云"：

<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>

该代码应解析此文件，并在该节点内文本中的任何位置查找包含术语"云"的节点的 ID。

我正在使用存在节点来找出这一点，但我没有得到正确的结果：

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;

我无法弄清楚我哪里出错了。

Answer 1

我将简单标签与列表标签分开，并在两个循环中搜索每个标签：

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;

输出是：

-------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'
---------------------