This discussion is archived
3 Replies Latest reply: Feb 8, 2013 4:56 AM by 698658 RSS

Extract value from XML column .

698658 Newbie
Currently Being Moderated
Hi,

I'm on 11.2.0.3 and got query
select other_xml from v$sql_plan where sql_id=’2y32qkr40b82a’ and other_xml is not null

<other_xml><info type="db_version">11.2.0.3</info>
<info type="parse_schema"><![CDATA["LIU"]]></info><info type="dynamic_sampling">2</info>
<info type="plan_hash">3882242470</info><info type="plan_hash_2">281948136</info>
<peeked_binds><bind nam=":N1" pos="1" dty="2" pre="0" scl="0" mxl="22">c30d51</bind>
<bind nam=":N2" pos="2" dty="2" pre="0" scl="0" mxl="22">c30d51</bind>
<bind nam=":N3" pos="3" dty="2" pre="0" scl="0" mxl="22">c30d51</bind></peeked_binds>
<outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint>
<hint><![CDATA[DB_VERSION('11.2.0.3')]]></hint>
<hint><![CDATA[FIRST_ROWS(100)]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint>
<hint><![CDATA[FULL(@"SEL$1" "LIU"@"SEL$1")]]></hint>
</outline_data></other_xml>
I want output related to <info type="dynamic_sampling">2</info> :

column name dynamic_sampling
row value 2

on the second thought using dynamic_sampling value as a predicate would be great so:

select * from v$sql_plan
where
dynamic_sampling = 2

how to transform other_xml column to have such results ?


Regards
GregG

Edited by: GregG on Feb 8, 2013 1:14 PM
  • 1. Re: Extract value from XML column .
    Dom Brooks Guru
    Currently Being Moderated
    XMLTABLE is a better way to do this as EXTRACTVALUE is deprecated but:
    select p.sql_id,  extractvalue(h.column_value,'/info') lvl
    from   v$sql_plan p
    ,      table(xmlsequence(extract(xmltype(p.other_xml),'/other_xml/info'))) h
    where  p.other_xml is not null
    and    extractvalue(h.column_value,'/info/@type') = 'dynamic_sampling';
    In 11.1 I think the tag might say "yes" rather than giving the level.
    If there's no dynamic sampling, then there should be no dynamic sampling tag.
  • 2. Re: Extract value from XML column .
    Dom Brooks Guru
    Currently Being Moderated
    And here is an xmltable version:
    select p.sql_id, t.val
    from   v$sql_plan p
    ,      xmltable('for $i in /other_xml/info
                     where $i/@type eq "dynamic_sampling"
                     return $i'
                    passing xmltype(p.other_xml)
                    columns attr varchar2(50) path '@type',
                            val  varchar2(50) path '/') t
    where  p.other_xml is not null;
    This works for me in 11.2.0.3 but crashes in 11.1.0.7 with ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [112], [2], [224], [], [], [], []
  • 3. Re: Extract value from XML column .
    698658 Newbie
    Currently Being Moderated
    Both working as intended . Thanks Dom :).
    GregG

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points