title: Finding repeating substrings in a string
body: <p>I am new to PL/SQL and I have been given this task.  I have XML messages that are being stored in a Receive_XML table with the field name of XML_DATA.  There has been issues where the sending location is sending split sequences which is causing them problems when they receive it back.  A split sequence is where we receive a sequence #1, then sequence #2, and then sequence #1 again.</p>
  
  <p>What would be the best way to search through the Receive_XML table to find instances where any sequence is repeated more than once without knowing the sequence number.  Here is an example of an XML record we receive.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;MESSAGE&gt;
   &lt;HEADER&gt;
    &lt;MESSAGE_TYPE&gt;ProductionSequence&lt;/MESSAGE_TYPE&gt;
    &lt;TIMESTAMP&gt;2019-10-29 10:00:01&lt;/TIMESTAMP&gt;
   &lt;/HEADER&gt;
   &lt;PRODUCTION_SEQUENCE&gt;
    &lt;SEQUENCES&gt;
     &lt;SEQUENCE SEQUENCE_ID="8764          " SEQUENCE_ID_PARENT=""&gt;
      &lt;PIECE_GROUPS&gt;
       &lt;PIECE_GROUP&gt;
        &lt;PIECE&gt;
         &lt;PIECE_ID&gt;514236947&lt;/PIECE_ID&gt;
        &lt;/PIECE&gt;
        &lt;PIECE&gt;
         &lt;PIECE_ID&gt;578645897&lt;/PIECE_ID&gt;
        &lt;/PIECE&gt;
       &lt;/PIECE_GROUP&gt;
      &lt;/PIECE_GROUPS&gt;
     &lt;/SEQUENCE&gt;
     &lt;SEQUENCE SEQUENCE_ID="6452          " SEQUENCE_ID_PARENT="8764"&gt;
      &lt;PIECE_GROUPS&gt;
       &lt;PIECE_GROUP&gt;
        &lt;PIECE&gt;
         &lt;PIECE_ID&gt;514236947&lt;/PIECE_ID&gt;
        &lt;/PIECE&gt;
        &lt;PIECE&gt;
         &lt;PIECE_ID&gt;578645897&lt;/PIECE_ID&gt;
        &lt;/PIECE&gt;
       &lt;/PIECE_GROUP&gt;
      &lt;/PIECE_GROUPS&gt;
     &lt;/SEQUENCE&gt;
     &lt;SEQUENCE SEQUENCE_ID="8764          " SEQUENCE_ID_PARENT="6452"&gt;
      &lt;PIECE_GROUPS&gt;
       &lt;PIECE_GROUP&gt;
        &lt;PIECE&gt;
         &lt;PIECE_ID&gt;514236947&lt;/PIECE_ID&gt;
        &lt;/PIECE&gt;
        &lt;PIECE&gt;
         &lt;PIECE_ID&gt;578645897&lt;/PIECE_ID&gt;
        &lt;/PIECE&gt;
       &lt;/PIECE_GROUP&gt;
      &lt;/PIECE_GROUPS&gt;
     &lt;/SEQUENCE&gt;
    &lt;/SEQUENCES&gt;
   &lt;/PRODUCTION_SEQUENCE&gt;
  &lt;/MESSAGE&gt;</code></pre>
  </div>
  </div>
  </p>
  
  <p>Thank you for any help you can provide.</p>
  
  <p>Bill</p>
  
  <hr>
  
  <p>What I finally found worked for me was:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select SEQUENCE_ID, count(SEQUENCE_ID) Counter
  from (
    select SEQUENCE_ID
    from   Receive_XML tbl,
         xmltable('/MESSAGE/PRODUCTION_SEQUENCE/SEQUENCES/SEQUENCE' 
                  PASSING xmltype(tbl.xml_data)
                  COLUMNS SEQUENCE_ID VARCHAR2(30) PATH '@SEQUENCE_ID')
    where tbl.id = 4451
  ) rs
  group by SEQUENCE_ID;
  </code></pre>