Home » Developer & Programmer » JDeveloper, Java & XML » XML_TYPE Problem
XML_TYPE Problem [message #357290] Tue, 04 November 2008 11:44 Go to next message
ORAGENASHOK
Messages: 207
Registered: June 2006
Location: Chennai
Senior Member
I have created one XMLTYPE Column in one table mentioned below
While inserting the data into that table it is successfully inserted.
when select the data from that table using the below query it will show error.


create table EI_XML_STORE_TEST
(
XML_ID varchar2(64),
XML_DATA XMLTYPE,
func_ind varchar2(20),
constraint EI_XML_STORE_TEST_PK primary key(XML_ID)
)


insert into EI_XML_STORE_TEST values('1','<response><rowset><row><payee>Ashok</payee><rem>Krish</rem>
</row><row><payee>Raj</payee><rem>Ram</rem>
</row></rowset></response>','Ind')


select xml_data.extractvalue('/response/rowset/row/payee') a,
xml_data.extractvalue('/response/rowset/row/rem') b
From EI_XML_STORE_TEST 


Error at line 3:
ORA-19025 : EXTACTVALUE return value of only one node.

while i am selecting the same data using the below query in pl/sql developer it throws the error as

OCI-21500 : internal error code,arguments :[58],[],[],[],[],[],[],[].
OCI-21500 : internal error code,arguments :[kghrh:ds],[0x23b6018],[],5


Please anyone post solution for this
Re: XML_TYPE Problem [message #357292 is a reply to message #357290] Tue, 04 November 2008 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-19025: EXTRACTVALUE returns value of only one node
 *Cause: Given XPath points to more than one node.
 *Action: Rewrite the query so that exactly one node is returned

What do you try to get?

Regards
Michel
Re: XML_TYPE Problem [message #357335 is a reply to message #357292] Tue, 04 November 2008 23:59 Go to previous messageGo to next message
ORAGENASHOK
Messages: 207
Registered: June 2006
Location: Chennai
Senior Member
I want to get the value like the below.

payee   rem
======  =====
Ashok   Krish
Raj     Ram


If the xml root have more than one value i want that one also like the above.

can you help me /post me the query to get this one.

[Updated on: Wed, 05 November 2008 00:01]

Report message to a moderator

Re: XML_TYPE Problem [message #357342 is a reply to message #357335] Wed, 05 November 2008 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select extractvalue(value(x),'/row/payee') a,
  2         extractvalue(value(x),'/row/rem') b
  3  from EI_XML_STORE_TEST, 
  4       table(xmlsequence(extract(xml_data,'/response/rowset/row'))) x
  5  /
A          B
---------- ----------
Ashok      Krish
Raj        Ram

2 rows selected.

Regards
Michel
Re: XML_TYPE Problem [message #357355 is a reply to message #357342] Wed, 05 November 2008 00:47 Go to previous messageGo to next message
ORAGENASHOK
Messages: 207
Registered: June 2006
Location: Chennai
Senior Member
Can you please explain about the below i can't understand

value(x)
table(xmlsequence(extract(xml_data,'/response/rowset/row'))) x
Re: XML_TYPE Problem [message #357361 is a reply to message #357342] Wed, 05 November 2008 01:10 Go to previous messageGo to next message
ORAGENASHOK
Messages: 207
Registered: June 2006
Location: Chennai
Senior Member
Michel,

Much more thanks to sort this problem.i have executed, its working fine. please also give the details about the below


value(x)
table(xmlsequence(extract(xml_data,'/response/rowset/row'))) x
Re: XML_TYPE Problem [message #357369 is a reply to message #357361] Wed, 05 November 2008 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
XML DB Developer's Guide
Chapter 4 XMLType Operations

Regards
Michel
Re: XML_TYPE Problem [message #358711 is a reply to message #357342] Wed, 12 November 2008 04:35 Go to previous messageGo to next message
ORAGENASHOK
Messages: 207
Registered: June 2006
Location: Chennai
Senior Member
I have one variable which contain xml format data datatype as XMLType/CLOB. i want to extract the node value from that variable without storing the xml data into table and then take it again using the query .can i directly use the below query for this process or if anyother way is there please tell me.

select extractvalue(value(x),'/row/payee') a,
       extractvalue(value(x),'/row/rem') b
     from dual
table(xmlsequence(extract([B]xmldata[/B],'/response/rowset/row'))) x



table(xmlsequence(extract([B]xmldata[/B],'/response/rowset/row'))) x


the xmldata is the varible name.I tried this one but it gives the error as SQL command not properly end
Re: XML_TYPE Problem [message #358728 is a reply to message #358711] Wed, 12 November 2008 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/353616/102589/#msg_353616

Regards
Michel
Re: XML_TYPE Problem [message #358743 is a reply to message #358728] Wed, 12 November 2008 06:08 Go to previous messageGo to next message
ORAGENASHOK
Messages: 207
Registered: June 2006
Location: Chennai
Senior Member
Here my requirement is we pass one xml format data through one clob variable from java as in parameter to stored procedure and i have decalare one variable as clob/xmltype to get that xml data in stored procedure and using that variable i want to extract the node values and have to store in the corresponding table.

The size of the data is also one of the constraints it may be 5MB of size we need to insert the data with small amount of time

[Updated on: Wed, 12 November 2008 06:49] by Moderator

Report message to a moderator

Re: XML_TYPE Problem [message #358755 is a reply to message #358743] Wed, 12 November 2008 06:51 Go to previous message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use code tags when it is not code. Code lines are not splitted on display.

And carefully read the link I posted.
Where does the xml come in the example?

Regards
Michel
Previous Topic: Join in TopLink
Next Topic: java.sql.SQLException: ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Goto Forum:
  


Current Time: Tue Sep 02 23:35:57 CDT 2014

Total time taken to generate the page: 0.07352 seconds