issues with namespace while Parsing XML in Oracle

Part of my XML is mentioned below.

<?xml version="1.0" encoding="utf-8"?>
<SubmitPart xmlns="http://www.w3schools.com/ravi/srpf"><OrderRequest><OrderXML><![CDATA[<OrderRetrieval xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.w3schools.com/ravi/srpf">
  <OrderDetails>
    <OrderProperties>
      <OrderID>40119</OrderID>
      <ProjectNumber/>
      <DefaultDueDate/>
      <BillingJurisdiction>Interstate</BillingJurisdiction>
      <ASRNumber>1006400257</ASRNumber>..........and so on

i am trying to get the ASRNumber value  1006400257 with the XMLTYPE function as below.
The xml is present in a column of an Oracle table.

select XMLTYPE(column_name).extract('/SubmitPart/OrderRequest() xmlns="http://www.w3schools.com/ravi/srpf"').getstringval() from table name.
I am getting the error message like 'ORA-31013: Invalid XPATH Expression'
Please provide me the solution or any other alternative.
my DB is Oracle 9.2.0.1.0
joe_suganthAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I've got some good news and some bad news.

Good news, there is almost always a work around.

Bad news.
First: the XML isn't valid.  The start tag doesn't match the end tag: <CirName>3DF/352762783//SFRTD</CircuitName>

Second:  The namespace is different in this latest example from the first one you posted.  The first one had "w3schools".  The latest had "W3SCHOOLS".

Third and most important:  There is a bug in 10g that I believe is also in 9i that doesn't properly extract large CDATA text values.  So, I used replace calls to strip out the CDATA wrappers.

The following example doesn't get RAG value since it is a straight forward XPATH that you should be able to come up with from the first SQL I posted.  Since CARType is an attribute, I used that in my example.



Do you have the ability to change the XML provided?  I've never seen XML wrapped in a CDATA tag.  I would gte rid of that if you could.  It would make the SQL a lot easier and more efficient.
select extractvalue(
	xmltype(
		replace(
			replace(
				extract(xmltype(col1),'/SubmitPart/OrderRequest/OrderXML/text()','xmlns="http://www.W3SCHOOLS.com/ravi/srpf"').getclobval(),'<![CDATA[',''
			),']]>',''
		)
	),'/OrderRetrieval/OrderDetails/Locations/Location/L1_Is/L1_I/@CARType','xmlns="http://www.w3schools.com/ravi/srpf"')
from tab1;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Can you post the complete XML?

From what you posted it looks like you have a nested XML doc embedded in a CDATA tag.  XML in 9.2 was fairly buggy especially in the first release (9.2.0.1).

If you can provide the complete XML I can try to get something working in 10.2 and I'll try to keep syntax that will work back in 9i but I make no promises.

also like the error states, '/SubmitPart/OrderRequest() xmlns="http://www.w3schools.com/ravi/srpf"' is not a valid XPATH expression.
0
 
joe_suganthAuthor Commented:
This is the full XML.

<?xml version="1.0" encoding="utf-8"?>
<SubmitPart xmlns="http://www.w3schools.com/ravi/srpf"><OrderRequest><OrderXML><![CDATA[<OrderRetrieval xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.w3schools.com/ravi/srpf">
  <OrderDetails>
    <OrderProperties>
      <OrderID>40119</OrderID>
      <ProjectNumber/>
      <DefaultDueDate/>
      <BillingJurisdiction>Interstate</BillingJurisdiction>
      <ASRNumber>1006400257</ASRNumber>
      <ServiceCheckIndicator>Yes</ServiceCheckIndicator>
      <CALCDate>03/11/2010</CALCDate>
    </OrderProperties>
    </OrderDetails>
</OrderRetrieval>]]></OrderXML><CurrentPage/><CUID/><LastModified>2010-03-10T15:05:35.129-06:00</LastModified><OrderID>40122</OrderID><LATA/></OrderRequest></SubmitPart>
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
slightwv (䄆 Netminder) Commented:
Check out:
http://petersnotes.blogspot.com/2008/03/xmltype-extract-on-xml-with-namespaces.html

This was tested in 10.2.0.3.
drop table tab1 purge;
create table tab1(col1 clob);
insert into tab1 values('<?xml version="1.0" encoding="utf-8"?>
<SubmitPart xmlns="http://www.w3schools.com/ravi/srpf"><OrderRequest><OrderXML><![CDATA[<OrderRetrieval xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.w3schools.com/ravi/srpf">
 <OrderDetails>
   <OrderProperties>
     <OrderID>40119</OrderID>
     <ProjectNumber/>
     <DefaultDueDate/>
     <BillingJurisdiction>Interstate</BillingJurisdiction>
     <ASRNumber>1006400257</ASRNumber>
     <ServiceCheckIndicator>Yes</ServiceCheckIndicator>
     <CALCDate>03/11/2010</CALCDate>
   </OrderProperties>
   </OrderDetails>
</OrderRetrieval>]]></OrderXML><CurrentPage/><CUID/><LastModified>2010-03-10T15:05:35.129-06:00</LastModified><OrderID>40122</OrderID><LATA/></OrderRequest></SubmitPart>');

commit;

select extractvalue(
	xmltype(
		extractvalue(xmltype(col1),'/SubmitPart/OrderRequest/OrderXML','xmlns="http://www.w3schools.com/ravi/srpf"')
	),'/OrderRetrieval/OrderDetails/OrderProperties/ASRNumber','xmlns="http://www.w3schools.com/ravi/srpf"')
from tab1;

Open in new window

0
 
joe_suganthAuthor Commented:
It works for that XML but the same solution doesn't work for other similar XML but big in size. Please find the attached XML and the data required are CARType value '1000 JEANS' and RAG value '14455555666666/RFAGAFAGAFAGSGFHS'

TEST.XML
0
 
joe_suganthAuthor Commented:
I am able ot get the CARType value. But while trying for RAG value with the query below,

select extractvalue(
xmltype(
extractvalue(xmltype(col1),'/SubmitPart/OrderRequest/OrderXML','xmlns="http://www.w3schools.com/ravi/srpf"')
),'/OrderRetrieval/OrderDetails/Locations/Location/L2_Is/I/IProperties/Edge/RAG','xmlns="http://www.w3schools.com/ravi/srpf"')
from table;
 I am getting the error
ora-01706 user function result value was too large
Please let me know how can i get rid of this. my db is Oracle 9.2.0.1.0
I have attached the corrected XML.

TEST.XML
0
 
joe_suganthAuthor Commented:
Dont get a any furthur details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.