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

user_19019233 0
Viewed 59

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.

oracle11g oracle xml sql
2 Answers

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

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

user_15092640
Related