Extract rows from oracle based on XML Clob search string

Question

We are using a product which store audit information in oracle database in XML clob. Below is the xml format

<Attributes>
  <Map>
    <entry key="messages">
      <value>
        <List>
          <Message key="err_exception" type="Error">
            <Parameters>
              <Message key="sailpoint.tools.GeneralException: The application script threw an exception: java.lang.Exception: This request could not be approved as you are the only available approval workgroup member; per CIS policy, a user is not allowed to approve their own request!! BSF info: script at line: 0 column: columnNo" type="Error"/>
            </Parameters>
          </Message>
        </List>
      </value>
    </entry>
  </Map>
</Attributes>

I'm using following query to get the values

select * from (select x.*
from identityiq.SPT_IDENTITY_REQUEST att,
      xmltable('Attributes'
      passing xmltype(att.attributes)
      columns Message varchar2(200) path '/Attributes/Map/entry[@key="messages"]/value/List/Message[@key="err_exception"]/Parameters/Message/@key' ) x
      where to_date('01/01/1970 00:00:00','mm-dd-yyyy HH24:MI:SS')+(att.created-14400000)/1000/60/60/24 <= to_date('10-01-2019 00:00:00', 'dd-mm-yyyy HH24:MI:SS') and
      to_date('01/01/1970 00:00:00','mm-dd-yyyy HH24:MI:SS')+(att.created-14400000)/1000/60/60/24 >= to_date('01-01-2019 00:00:00', 'dd-mm-yyyy HH24:MI:SS')) res
      where res.message is not null 
      and res.message like '%CIS policy%';

But I'm getting following error

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 -  "XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" 
*Cause:    The XQuery sequence passed in had more than one item.
*Action:   Correct the XQuery expression to return a single item sequence.

Any help would be greately appreciated

score 1 · Accepted Answer · answered Feb 13 '19 at 17:40

Your xml data does not actually match the xml format you gave us. :)

One of the levels in your XPath expression is returning 2 nodes instead of 1. I'm guessing it's the <List> node which has multiple <Message> children, but it could be anything. Here's how you solve that problem:

-- sample data
with SPT_IDENTITY_REQUEST as (select to_clob('<Attributes>
  <Map>
    <entry key="messages">
      <value>
        <List>
          <Message key="err_exception" type="Error">
            <Parameters>
              <Message key="CIS policy sample exception #1" type="Error"/>
            </Parameters>
          </Message>
          <Message key="err_exception" type="Error">
            <Parameters>
              <Message key="CIS policy sample exception #2" type="Error"/>
            </Parameters>
          </Message>
        </List>
      </value>
    </entry>
  </Map>
</Attributes>') as attributes from dual)
-- your query
select * from (select *
from identityiq.SPT_IDENTITY_REQUEST att,
      xmltable('Attributes'
        passing xmltype(att.attributes)
        columns MessageXML XMLType path '/Attributes/Map/entry[@key="messages"]/value/List/Message[@key="err_exception"]' ) x,
      xmltable('/Message' -- add a second xmltable to handle multiple nodes at this level
        passing x.MessageXML
        columns Message varchar2(100) path '/Message/Parameters/Message/@key') m
      ) res
      where res.message is not null 
      and res.message like '%CIS policy%';

See also this answer to a similar question, which covers it in more depth.

Thanks for quick response @kfinity. I agree that we might be getting two children. but how to handle if we have more than two children in output. If it is two children then as you mentioned we can treat that in xmltable. But if it is more than two children, how can we handle it? (2) Can we add in Xpath like this .. key like '%%' so that we can pick only required children, Please let me know. — jks, Feb 14 '19 at 14:06
The code above should work fine for multiple Messages - but if you have multiple children under other nodes, like multiple Parameters in a Message, or multiple Messages in a Parameters node, then you'll need to add more xmltables. — kfinity, Feb 14 '19 at 14:57
And yes, see this question for how to do an Xpath to match attributes that contain a string. https://stackoverflow.com/questions/103325/what-is-the-correct-xpath-for-choosing-attributes-that-contain-foo — kfinity, Feb 14 '19 at 14:59

Extract rows from oracle based on XML Clob search string

1 Answers1