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 |