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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.