This discussion is archived
1 Reply Latest reply: Oct 24, 2012 11:25 PM by AlexAnd RSS

path query to Xquery

NinaOracle Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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> 

Legend

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