XDB - XML processing (Anyone here using it)

[grjohnson]

Just wondered about peoples experience with Oracle 9.2 XDB. I've been utilising it and was wanting to know other people opinion.

I created a table utilising the XMYTYPE and then also implemented the XDB functionality. As far I I can tell, (apart from the slightly different query syntax). The functionality bvetween the two is the same?

Can anyone add to this?

Cheers,

[grjohnson]

Test..

select extractValue(value(x),'/PurchaseOrder/ShippingInstructions/name')
from purchaseorder x
where existsNode(value(x),
'/PurchaseOrder/LineItems/LineItem/Part[@Id="037429139523"]') = 1

EXTRACTVALUE(VALUE(X
--------------------
Michael L. Allen
Edward K. Clark
Stephen B. Ward
Stephen B. Ward
.
.
.
Richard J Jones
Thomas D. Martin
Stephen B. Ward

39 rows selected.

Now I want the DISTINCT names in the table... how?

select extractValue(distinct(value(x)),'/PurchaseOrder/ShippingInstructions/name')
from purchaseorder x
where existsNode(value(x),
'/PurchaseOrder/LineItems/LineItem/Part[@Id="037429139523"]') = 1


EXTRACTVALUE(DISTINC
--------------------
Michael L. Allen
Edward K. Clark
Stephen B. Ward
Stephen B. Ward

.
.
.
Richard J Jones
Thomas D. Martin
Stephen B. Ward

39 rows selected.

Obviously not...hmmm...

[grjohnson]

Here is another issue..

Get all cost centres for USERS with the name of ADAMS

select extractValue(value(x),'/PurchaseOrder/CostCenter')
from purchaseorder x
where existsNode(value(x),
'/PurchaseOrder[User="ADAMS"]') = 1
EXTR
----
R20
R20
R20
R20
R20
R20
R20

7 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=1911)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PURCHASEORDER' (Cost=3
Card=1 Bytes=1911)
2 1 INDEX (RANGE SCAN) OF 'IPURCHASEORDERUSER' (NON-UNIQUE)
(Cost=1 Card=1)

We get an index scan... BUT when we do a traditional XPath query..

Then we get this crap...

SQL> select extractValue(value(x),'/PurchaseOrder[User="ADAMS"]/CostCenter')
from purchaseorder x;

EXTR
----
R20
R20


168 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=3822)
1 0 TABLE ACCESS (FULL) OF 'PURCHASEORDER' (Cost=4 Card=2 Bytes=3822)


Therefore to get rid of the extra 160 odd BLANK records.. have to add a NOT NULL restriction

SQL> select extractValue(value(x),'/PurchaseOrder[User="ADAMS"]/CostCenter')
from purchaseorder x
where extractValue(value(x),'/PurchaseOrder[User="ADAMS"]/CostCenter') is NOT NULL;

EXTR
----
R20
R20
R20
R20
R20
R20
R20

7 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=1911)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'PURCHASEORDER' (Cost=4 Card=1 Bytes=1911)
3 1 FILTER
4 3 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=16360)


Then we get FTS's. Well that'd be great Query performance if querying a multi-million row table.

Oracle DOESN'T handle pure XPath querying. Oracle will only use an index lookup if the XPath restriction is in the WHERE (from my examples). So how does Oracle claim to have inplemented full XPATH functionality?

[grjohnson]

Hi again.. I'm just putting these test results up for people who are interested and may be able to comment on it.

Using DISTINCT...

SQL> select distinct(extractValue(value x),'/PurchaseOrder/CostCenter'))
from purchaseorder x;

(EXT
----
A10
R20
S30

SQL> select distinct(extractValue(value(x),'/PurchaseOrder/CostCenter'))
from purchaseorder x
where existsNode(value(x),
'/PurchaseOrder/LineItems/LineItem/Part[@Id="037429139523"]') = 1;

select distinct(extractValue(value(x),'/PurchaseOrder/CostCenter'))
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kokbtns1], [], [], [], [], [], [],
[]

Just to prove that query works without the DISTINCT...

SQL> select extractValue(value(x),'/PurchaseOrder/CostCenter')
from purchaseorder x
where existsNode(value(x),'/PurchaseOrder/LineItems/LineItem/Part[@Id="037429139523"]') = 1;

EXTR
----
S30
A10
S30
.
.
.
R20
S30
S30

39 rows selected.