Home » Developer & Programmer » JDeveloper, Java & XML » XMLTYPE issue in 10g (10.2.0.4.0)
XMLTYPE issue in 10g [message #505296] Mon, 02 May 2011 02:55 Go to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,

We are in process of Migrating our database from Oracle 9i to Oracle 10g.

I am getting below error while parsing XML in 10g.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '//soap:Envelope/soap:Header/coHeader/company/text()'

Same code works fine in Oracle 9i database with same XML.

Is there any difference in XMLTYPE functionality in Oracle 9i and 10g?

Many Thanks,
Amit.
Re: XMLTYPE issue in 10g [message #505299 is a reply to message #505296] Mon, 02 May 2011 03:22 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

CREATE TABLE XML_TABLE
(
XML_COL SYS.XMLTYPE
)


declare
l_temp sys.xmlType;
begin
l_temp := sys.xmlType.createXML('<?xml version="1.0" encoding="utf-8" ?>
<soap:Envelope xmlns:xsi="http://www.w4.org/2004/XMLSchema-instance" xmlns:xsd="http://www.w4.org/2004/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<coHeader>
<company>AAA</company>
</coHeader>
</soap:Header>
</soap:Envelope>'
);
insert into xml_table values ( l_Temp);
Commit;
end;

select a.xml_col.extract('//soap:Envelope/soap:Header/coHeader/company/text()').getStringVal()
from xml_table a


Oracle 9i Output: AAA

Oracle 10g: Error
Re: XMLTYPE issue in 10g [message #505302 is a reply to message #505299] Mon, 02 May 2011 03:37 Go to previous messageGo to next message
_jum
Messages: 573
Registered: February 2008
Senior Member
Give EXTRACT the namespace string:
SELECT a.xml_col.extract('//soap:Envelope/soap:Header/coHeader/company/text()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"').getStringVal()
  FROM xml_table a;

[Updated on: Mon, 02 May 2011 05:34] by Moderator

Report message to a moderator

Re: XMLTYPE issue in 10g [message #505303 is a reply to message #505299] Mon, 02 May 2011 03:37 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
According to this here in 10 you would have to define the namespace when you want to use it.

So in your case you have to define the soap: namespace :

select a.xml_col.extract('//soap:Envelope/soap:Header/coHeader/company/text()',
       'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"').getStringVal()
from xml_table a
Re: XMLTYPE issue in 10g [message #505306 is a reply to message #505303] Mon, 02 May 2011 04:20 Go to previous message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,

Thanks a lot for your solution. It worked. Smile

Is there any way we can make a global change for this? I am extracting values from XML in my PL/SQL block.

I might have to make lots of changes for this.

Thank you for your support.
Previous Topic: Reading data from database table that contains control characters.
Next Topic: Connection reset by peer socket write error
Goto Forum:
  


Current Time: Fri Jul 30 09:27:17 CDT 2021