How to count how many times a tag appear inside a CLOB PLSQL oracle 11g

Question

i have a clob column in a table. In this clob i have a XML and i want to count how many times a tag appear inside this clob.

For example:

<TPQ>       
    <LTP>N<LTP> 
        <SUBLTP>N</SUBLTP>
        <TIMES>446</TIMES>
        <TIMES>321</TIMES>
        <TIMES>546</TIMES>
        <TIMES>547</TIMES>
    <LTP>N</LTP>    
    <LTP2>N<LTP2>   
        <SUBLTP>N</SUBLTP>
        <NODES>1</NODES>
        <NODES>2</NODES>
        <NODES>3</NODES>
        <NODES>4</NODES>
        <SUBLTP>H</SUBLTP>
        <SUBLTP3>A</SUBLTP3>
        <SUBLTP2>N</SUBLTP2>
    <LTP2>N</LTP2>  
</TPQ>

I want to know that the tag "TIMES" appears 4 times, and tag "NODES" appears 4 times.

Im using this query for getting all TIMES tag but i need know how to count:

SELECT EXTRACT(xmltype.createxml(T.columnCLOB), '//TPQ/LTP/TIMES').getStringVal()
  FROM table1 T; 

and the result is this:

Result of the Select Statement is

<TIMES>446</TIMES><TIMES>321</TIMES><TIMES>546</TIMES><TIMES>547</TIMES>

This in a example, i need a solution for a dinamic clob column that can have x tags inside, not always with the same structure. But i only need to know how many times appears a specified tag.

Answer 1

You can use:

SELECT t.id,
       x.tag_name,
       COUNT(*)
FROM   table_name t
       CROSS JOIN XMLTABLE(
         '//*'
         PASSING XMLTYPE(t.xml) 
         COLUMNS 
           tag_name varchar2(100) path 'name()'    
       ) x
GROUP BY t.id, x.tag_name

Which, for the sample data:

CREATE TABLE table_name (id NUMBER, xml CLOB);

INSERT INTO table_name (id, xml)
VALUES (1, '<TPQ>       
    <LTP>N</LTP> 
        <SUBLTP>N</SUBLTP>
        <TIMES>446</TIMES>
        <TIMES>321</TIMES>
        <TIMES>546</TIMES>
        <TIMES>547</TIMES>
    <LTP>N</LTP>    
    <LTP2>N</LTP2>   
        <SUBLTP>N</SUBLTP>
        <NODES>1</NODES>
        <NODES>2</NODES>
        <NODES>3</NODES>
        <NODES>4</NODES>
        <SUBLTP>H</SUBLTP>
        <SUBLTP3>A</SUBLTP3>
        <SUBLTP2>N</SUBLTP2>
    <LTP2>N</LTP2>  
</TPQ>');

Outputs:

ID	TAG_NAME	COUNT(*)
1	LTP	2
1	LTP2	2
1	SUBLTP2	1
1	NODES	4
1	TPQ	1
1	SUBLTP	3
1	TIMES	4
1	SUBLTP3	1

---

If you only want a specific tag name and want to aggregate the tags' contents then:

SELECT t.id,
       x.tag_name,
       COUNT(*),
       LISTAGG(x.value, ',') WITHIN GROUP (ORDER BY value) AS contents
FROM   table_name t
       CROSS JOIN XMLTABLE(
         '//TIMES'
         PASSING XMLTYPE(t.xml) 
         COLUMNS 
           tag_name VARCHAR2(100) PATH 'name()',
           value    VARCHAR2(4000) PATH 'text()'
       ) x
GROUP BY t.id, x.tag_name

Which outputs:

ID	TAG_NAME	COUNT(*)	CONTENTS
1	TIMES	4	321,446,546,547

db<>fiddle here