1 Reply Latest reply: Jan 30, 2013 12:16 AM by AlexAnd RSS

    xml

    va*447258*15
      hi All,

      i am trying to understand xmltable path from following example from otn forum:

      please can anyone expain following line from following select command as i am new in xml.

      Please anyone could let me know about any article about xmltable and path?
      create table data_table (
        data xmltype
        );
      
      insert into data_table values (
          xmltype('<?xml version="1.0" encoding="UTF-8"?>
          <item_result title="M1Q14" presented="No">
            <result_metadata>
              <object_id>_11147_1</object_id>
              <result_object_id>2638013</result_object_id>
              <bbmd_resulttype>Item</bbmd_resulttype>
            </result_metadata>
            <date>
             <type_label>Item Creation Time.</type_label>
             <datetime>2011:09:16T19:47:18</datetime>
           </date>
         <response ident_ref="response">
             <response_form cardinality="Single" render_type="choice" timing="No" response_type="lid">
               <extension_responseform>
                 <answer_order>0,1,3,2</answer_order>
               </extension_responseform>
             </response_form>
             <response_value response_status="Valid" response_time="-1">0</response_value>
           </response>
         <outcomes status="Completed">
             <score varname="MANUAL" vartype="Decimal" status="Valid">
               <score_value/>
             </score>
             <score varname="AUTO" vartype="Decimal" status="Valid">
               <score_value>5.0</score_value>
               <score_maximum>5.0</score_maximum>
             </score>
           </outcomes>
         </item_result>')
         );
      
      
      select x.*
          from data_table t
             , xmltable('/item_result'
                 passing t.data
                 columns title            varchar2(30) path '@title'
                       , presented        varchar2(3)  path '@presented'
                       , object_id        varchar2(30) path 'result_metadata/object_id'
                       , result_object_id varchar2(30) path 'result_metadata/result_object_id'
                       , bbmd_resulttype  varchar2(30) path 'result_metadata/bbmd_resulttype'
                      , type_label       varchar2(30) path 'date/type_label'
                      , datetime         varchar2(30) path 'date/datetime'
                      , score_value      number       path 'outcomes/score[score_value/text()]/score_value'
                      , score_maximum    number       path 'outcomes/score[score_value/text()]/score_maximum'
              ) x
        ;
      Best Regards,