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

    path query to Xquery

    NinaOracle
      if I have this xmltable



      CREATE TABLE xmltable OF XMLType;

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

      <seller_info>

      <seller_name> cubsfantony</seller_name>

      <seller_rating> 848</seller_rating>

      </seller_info>

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

      </payment_types>

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

      </shipping_info>

      <buyer_protection_info>

      </buyer_protection_info>

      <auction_info>

      <current_bid>$620.00 </current_bid>

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

      <high_bidder>

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

      <bidder_rating>-2 </bidder_rating>

      </high_bidder>

      <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>

      </auction_info>

      <bid_history>

      <highest_bid_amount>$620.00 ; </highest_bid_amount>

      <quantity> 1</quantity>

      </bid_history>

      <item_info>

      <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>

      </item_info>

      </listing></root>'

      ));



      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

      as

      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

      regards
      <div id="isChromeWebToolbarDiv" style="display:none"></div>
        • 1. Re: path query to Xquery
          AlexAnd
          >
          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

          >
          "$100.00"
          >
          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;
           
          TIME_LEFT
          --------------------------------------------------------------------------------
           4 days, 14 hours +
           
          SQL>