Search code examples
sqlxmlplsqloracle11gclob

Substr more than one value in a Oracle CLOB column

I have a clob column in a database table which has a lot of XML text. I'm trying to grab all the Exceptions from the clob by using SQL or PL/SQL, the exceptions come under an XML tab of ExceptionList as you can see below. How would I just get the text string "Both HPI Make and HPI Category have to be entered - one cannot exist without the other" from the below example in the clob and also get the other Exception descriptions "Exception List must be empty","AgreementNumber must not be supplied","IntroducerCode or Name is invalid" and "SupplierCode or Name invalid. Must exist on pancredit" which are the Exception Descriptions. There can be other exceptions but I'm just trying to get all the exception descriptions (i.e. substr anything between the XML tab tns:Description and </tns:Description>) in the clob.

Would I do this in an PL/SQL LOOP or could this be done using SQL?

<tns:ExceptionList>
      <tns:Exception>
        <tns:Description>Both HPI Make and HPI Category have to be entered - one cannot exist without the other.
</tns:Description>
      </tns:Exception>
      <tns:Exception>
        <tns:Description>Exception List must be empty</tns:Description>
      </tns:Exception>
      <tns:Exception>
        <tns:Description>AgreementNumber must not be supplied</tns:Description>
      </tns:Exception>
      <tns:Exception>
        <tns:Description>IntroducerCode or Name is invalid</tns:Description>
      </tns:Exception>
      <tns:Exception>
        <tns:Description>SupplierCode or Name invalid. Must exist on pancredit.</tns:Description>
      </tns:Exception>
    </tns:ExceptionList>

Thanks in advance.

Solution

  • If you have XML then treat is as XML, don't try to extract data from it using substrings. Presumably this is a fragment of a larger document that has parent nodes and declares the tns namespace; so you can do:

    select x.description
from your_table t
cross apply xmltable(
  xmlnamespaces('https://some/url' as "tns"),
  '//tns:ExceptionList/tns:Exception'
  passing xmltype(t.your_clob)
  columns description path 'tns:Description'
) x
    DESCRIPTION
    Both HPI Make and HPI Category have to be entered - one cannot exist without the other.<br>
    Exception List must be empty
    AgreementNumber must not be supplied
    IntroducerCode or Name is invalid
    SupplierCode or Name invalid. Must exist on pancredit.

    db<>fiddle