XML : ORA-19025 - too many nodes when using extractValue

How can i select for the value "USPrice" if PartNumber="926-AA"??

My select already end in ORA-19025!

  SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Items/Item/USPrice/text()') "PRICE" FROM TMP_TABLE  WHERE extractValue(OBJECT_VALUE,'/PurchaseOrder/Items/Item[@PartNumber]') = '926-AA';    

Here is the DDL:

  CREATE TABLE TMP_TABLE OF XMLType;  INSERT INTO TMP_TABLE VALUES (XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'),nls_charset_id('AL32UTF8')));    

Here is the data:

  <PurchaseOrder PurchaseOrderNumber="99503" OrderDate="1999-10-20">    <Address Type="Shipping">      <Name>Ellen Adams</Name>      <Street>123 Maple Street</Street>      <City>Mill Valley</City>      <State>CA</State>      <Zip>10999</Zip>      <Country>USA</Country>    </Address>    <Address Type="Billing">      <Name>Tai Yee</Name>      <Street>8 Oak Avenue</Street>      <City>Old Town</City>      <State>PA</State>      <Zip>95819</Zip>      <Country>USA</Country>    </Address>    <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>    <Items>      <Item PartNumber="872-AA">        <ProductName>Lawnmower</ProductName>        <Quantity>1</Quantity>        <USPrice>148.95</USPrice>        <Comment>Confirm this is electric</Comment>      </Item>      <Item PartNumber="926-AA">        <ProductName>Baby Monitor</ProductName>        <Quantity>2</Quantity>        <USPrice>39.98</USPrice>        <ShipDate>1999-05-21</ShipDate>      </Item>    </Items>  </PurchaseOrder>    

Thank you in advance

No comments:

Post a Comment