3 Replies Latest reply: Feb 8, 2013 6:56 AM by 698658 RSS

    Extract value from XML column .


      I'm on 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"></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>
      <hint><![CDATA[FULL(@"SEL$1" "LIU"@"SEL$1")]]></hint>
      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
      dynamic_sampling = 2

      how to transform other_xml column to have such results ?


      Edited by: GregG on Feb 8, 2013 1:14 PM
        • 1. Re: Extract value from XML column .
          Dom Brooks
          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
            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 but crashes in with ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [112], [2], [224], [], [], [], []
            • 3. Re: Extract value from XML column .
              Both working as intended . Thanks Dom :).