Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

using extractvalue with XMLTYPE to extract multiple nodes

699494Sep 11 2010 — edited Sep 11 2010

hi ,

I'm trying to fetch data from xml which is stored as clob in db (10g) ,

when i tried to fetch with query as below

SELECT extractvalue(XMLTYPE(xjm.X_MSG_XML), '//orderRequest/order/orderItem/alternativeIdentifer/value') uiid
FROM TABLE_X_MSG_ATTR xma, TABLE_X_JMS_MSG xjm
WHERE xma.X_MSG_ATTR2X_JMS_MSG=xjm.OBJID
AND xma.X_ATTR_NAME='MOP_ID'
AND xma.X_ATTR_VALUE in ('RJM78999')

and got **ORA-19025: EXTRACTVALUE returns value of only one node**

so i modified it as below

SELECT extractvalue(value(x), '//orderRequest/order/orderItem/alternativeIdentifer/value') uiid
FROM TABLE_X_MSG_ATTR xma, TABLE_X_JMS_MSG xjm
,TABLE(XMLSequence(extractvalue(XMLTYPE(xjm.X_MSG_XML),'//orderRequest/order/orderItem/alternativeIdentifer/value'))) x
WHERE xma.X_MSG_ATTR2X_JMS_MSG=xjm.OBJID
AND xma.X_ATTR_NAME='MOP_ID'
AND xma.X_ATTR_VALUE in ('RJM78999')

ORA-29900: operator binding does not exist
ORA-06553: PLS-306: wrong number or types of arguments in call to 'XMLSEQUENCE'

no success :( it is working for single record (node)all perfect

Thnkz

Edited by: Volution2111 on Sep 11, 2010 5:08 AM

Edited by: Volution2111 on Sep 11, 2010 5:10 AM

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 9 2010

Added on Sep 11 2010

#db, #performance-availability, #xml, #xml-db

1 comment

3,038 views