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><Or
<OrderDetails>
<OrderProperties>
<OrderID>40119</OrderID>
<ProjectNumber/>
<DefaultDueDate/>
<BillingJurisdiction>Inter
<ASRNumber>1006400257</ASR
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).extra
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
<?xml version="1.0" encoding="utf-8"?>
<SubmitPart xmlns="http://www.w3schools.com/ravi/srpf"><OrderRequest><Or
<OrderDetails>
<OrderProperties>
<OrderID>40119</OrderID>
<ProjectNumber/>
<DefaultDueDate/>
<BillingJurisdiction>Inter
<ASRNumber>1006400257</ASR
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).extra
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
ASKER
This is the full XML.
<?xml version="1.0" encoding="utf-8"?>
<SubmitPart xmlns="http://www.w3schools.com/ravi/srpf"><OrderRequest><Or
<OrderDetails>
<OrderProperties>
<OrderID>40119</OrderID>
<ProjectNumber/>
<DefaultDueDate/>
<BillingJurisdiction>Inter
<ASRNumber>1006400257</ASR
<ServiceCheckIndicator>Yes
<CALCDate>03/11/2010</CALC
</OrderProperties>
</OrderDetails>
</OrderRetrieval>]]></Orde
<?xml version="1.0" encoding="utf-8"?>
<SubmitPart xmlns="http://www.w3schools.com/ravi/srpf"><OrderRequest><Or
<OrderDetails>
<OrderProperties>
<OrderID>40119</OrderID>
<ProjectNumber/>
<DefaultDueDate/>
<BillingJurisdiction>Inter
<ASRNumber>1006400257</ASR
<ServiceCheckIndicator>Yes
<CALCDate>03/11/2010</CALC
</OrderProperties>
</OrderDetails>
</OrderRetrieval>]]></Orde
Check out:
http://petersnotes.blogspo
This was tested in 10.2.0.3.
http://petersnotes.blogspo
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;
ASKER
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/RFAGAFAGAF
TEST.XML
TEST.XML
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am able ot get the CARType value. But while trying for RAG value with the query below,
select extractvalue(
xmltype(
extractvalue(xmltype(col1)
),'/OrderRetrieval/OrderDe
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
select extractvalue(
xmltype(
extractvalue(xmltype(col1)
),'/OrderRetrieval/OrderDe
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
ASKER
Dont get a any furthur details.
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(