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

kfinity · Accepted Answer · 2019-02-13 17:40:16Z

1

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.

answered Feb 13, 2019 at 17:40

kfinity

9,1411 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jks Over a year ago

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 '%<required string>%' so that we can pick only required children, Please let me know.

kfinity Over a year ago

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 Over a year ago

And yes, see this question for how to do an Xpath to match attributes that contain a string. stackoverflow.com/questions/103325/…

Collectives™ on Stack Overflow

Extract rows from oracle based on XML Clob search string

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related