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

    Extract value from XML column .

    698658
      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
          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 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
              Both working as intended . Thanks Dom :).
              GregG