Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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>');
1 rows affected

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


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


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


ID
TAG_NAME
COUNT(*)
CONTENTS


1
TIMES
4
321,446,546,547