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