title: Oracle SQL - Count XML nodes in a clob type column
body: <p>I was trying to write an optimized SQL query to count a specific XML node in clob type column, for now I'm using a REGEXP_COUNT to browse through all rows and all XMLs, but this is terribly slow. </p>
  
  <p>Like this:  </p>
  
  <pre><code>SELECT sum(REGEXP_COUNT(clobtype_column_withXMLs, '/Node_Closing_tag', 1)) as 'Node_number' FROM MY_TABLE;
  </code></pre>
  
  <p>This works, but with hundreds of millions of tags it takes literally ages.</p>
  
  <p>I want to bypass the XML structure and shorten the count time. I have found some things, but most of them involve changing the column type to xml and use xquery and other DML functions.<br>
  In my case I can't modify the table, I only need the number, how many times the specified node occurs in the column.</p>
  
  <p>My table structure:</p>
  
  <p>not an XML Table, various column types and a <code>CLOB type column</code>, in which every row contains a different sized XML (hundreds of millions of characters).
  The XML has pretty complex structure, the one node I'm searching for is in the middle:</p>
  
  <pre><code>1/2/3/4/5/I'm_searching_for_this/contains_multiple_nodes
  </code></pre>
  
  <p>Is there any fast way to count nodes in a structure like this, without modifying anything in the table? With fast I mean, <strong>count 500 million nodes in a couple of hours maximum</strong>. (text search takes 24h+)</p>