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 Select

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.

CodePudding user response：

XPATH functions can be used

with
  x as
    (select xmltype('<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>') xval
from dual)
select z.*
from x, xmltable ('count(/TPQ/TIMES)' passing x.xval) z;

CodePudding user response：

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

db<>fiddle here