I created the following view... but I want to have an index on the '//Part/@UnitPrice'
create or replace view vw_purchaseorders
(TITLE, QUANTITY, PRICE) as
SELECT
extractValue(value(i),'//Description/text()'),
extractvalue(value(i), '//Part/@Quantity'),
extractvalue(value(i), '//Part/@UnitPrice')
FROM purchaseorders x,
table(xmlsequence(x.data.extract
'/PurchaseOrder/LineItems/LineItem'))) i;
View Created
How do I create the index on the underlying tag //Part/@UnitPrice'? It's a fair bit different to the Mark Drake examples..
e.g.
create index iPurchaseOrderUser on PurchaseOrder x
(extractValue(value(x), '/PurchaseOrder/User'))
Because USER is a tag in itself.
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNames
paceSchemaLocation="http://147.69.216.14:8181/home/SCOTT/xsd/purchaseOrder.xsd">
<Reference>ADAMS-20011127121040988PST</Reference>
<Actions>
<Action>
<User>SCOTT</User>
</Action>
</Actions>
<Reject/>
<Requestor>Julie P. Adams</Requestor>
<User>ADAMS</User>
<CostCenter>R20</CostCenter>
<ShippingInstructions>
<name>Julie P. Adams</name>
<address>300 Oracle Parkway, Redwood Shores, CA 94065</address>
<telephone>650 506 7300</telephone>
</ShippingInstructions>
<SpecialInstructions>Ground</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>The Ruling Class</Description>
<Part Id="715515012423" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>Diabolique</Description>
<Part Id="037429135020" UnitPrice="29.95" Quantity="3"/>
Thanks,