1 Reply Latest reply on Oct 25, 2012 6:25 AM by AlexAnd

    path query to Xquery

      if I have this xmltable

      CREATE TABLE xmltable OF XMLType;

      INSERT INTO xmltable VALUES (XMLType('<root><listing>


      <seller_name> cubsfantony</seller_name>

      <seller_rating> 848</seller_rating>


      <payment_types>Visa/MasterCard, Money Order/Cashiers Checks, Personal Checks, See item description for payment methods accepted


      <shipping_info>Buyer pays fixed shipping charges, Will ship to United States only





      <current_bid>$620.00 </current_bid>

      <time_left> 4 days, 14 hours + </time_left>


      <bidder_name> gosha555@excite.com </bidder_name>

      <bidder_rating>-2 </bidder_rating>


      <num_items>1 </num_items>

      <num_bids> 12</num_bids>

      <started_at>$1.00 </started_at>

      <bid_increment> </bid_increment>

      <location> USA-Chicago</location>

      <opened> Nov-27-00 04:57:50 PST</opened>

      <closed> Dec-02-00 04:57:50 PST</closed>

      <id_num> 511601118</id_num>

      <notes> </notes>



      <highest_bid_amount>$620.00 ; </highest_bid_amount>

      <quantity> 1</quantity>



      <memory> 256MB PC133 SDram</memory>

      <hard_drive> 30 GB 7200 RPM IDE Hard Drive</hard_drive>

      <cpu>Pentium III 933 System </cpu>

      <brand> </brand>

      <description> </description>




      I want to retrieve the path query //auction_info[current_bid=" $610.00";][num_items=" 1"][started_at=" $100.00";]/time_left

      in XQuery or using other operators


      Select extract(OBJECT_VALUE,'//auction_info[current_bid=" $610.00";][num_items=" 1"][started_at=" $100.00";]/time_left')

      from xml_table;

      but it does not work, How I write the right query for this path

      <div id="isChromeWebToolbarDiv" style="display:none"></div>
        • 1. Re: path query to Xquery
          Select extract(OBJECT_VALUE,'//auction_info[current_bid=" $610.00";][num_items=" 1"][started_at=" $100.00";]/time_left')
          incorrect to
          <current_bid>$620.00 </current_bid>
          <started_at>$1.00 </started_at>
          also you have space

          not eq
          "$100.00 "

          one way
          SQL> select x.time_left
            2    from txmltable t, XMLTable('for $i in /root/listing/auction_info
            3                                  where normalize-space($i/current_bid) eq "$620.00"
            4                                    and normalize-space($i/num_items) eq "1"
            5                                    and normalize-space($i/started_at) eq "$1.00"
            6                                  return $i/time_left'
            7                                 passing t.object_value
            8                                columns time_left varchar2(4000) path '.'
            9                                 ) x;
           4 days, 14 hours +