This discussion is archived
1 Reply Latest reply: Jan 29, 2013 10:16 PM by AlexAnd RSS

xml

va*447258*15 Newbie
Currently Being Moderated
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,

Legend

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