| XML_TYPE Problem [message #357290] |
Tue, 04 November 2008 11:44  |
ORAGENASHOK
Messages: 240
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 #357335 is a reply to message #357292] |
Tue, 04 November 2008 23:59  |
ORAGENASHOK
Messages: 240
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 #357355 is a reply to message #357342] |
Wed, 05 November 2008 00:47 |
ORAGENASHOK
Messages: 240
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 |
ORAGENASHOK
Messages: 240
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 #358711 is a reply to message #357342] |
Wed, 12 November 2008 04:35 |
ORAGENASHOK
Messages: 240
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 #358743 is a reply to message #358728] |
Wed, 12 November 2008 06:08 |
ORAGENASHOK
Messages: 240
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 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
