Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, phil.

Asked: November 23, 2004 - 8:40 pm UTC

Last updated: February 05, 2018 - 11:22 am UTC

Version: 9.2.0.5

Viewed 10K+ times! This question is

You Asked

I have some xml that has a namespace defined in a node within a namespace node ( see Ah:AppHdr ), I would like to extract the value of the element Ah:MsgRef .Can I extract it, if so how? When I try I get an XPATH error.

declare
l_req xmlType := xmlType('<?xml version=''1.0'' encoding=''UTF-8''?>'||
'<SwInt:HandleRequest xmlns:Sw="urn:swift:snl:ns.Sw" xmlns:SwInt="urn:swift:snl:ns.SwInt" xmlns:SwSec="urn:swift:snl:ns.SwSec">'||
'<SwInt:RequestHandle>'||
'<SwInt:RequestDescriptor>'||
'<SwInt:SwiftRequestRef>SNL00115-2003-07-19T08:29:30.326.000005Z</SwInt:SwiftRequestRef>'||
'<SwInt:SwiftRef>swi00002-2003-07-19T08:38:54.15526.243389Z</SwInt:SwiftRef> '||
'<SwInt:NonRep>'||
'<SwInt:NRType>SVCMAND</SwInt:NRType>'||
'</SwInt:NonRep>'||
'<SwInt:ValidationDescriptor>'||
'<SwInt:ValResult>Success</SwInt:ValResult>'||
'</SwInt:ValidationDescriptor>'||
'<Sw:SnFOutputInfo>'||
'<Sw:SnFSessionId>swhqbebb_Fundsmsg:p:000028</Sw:SnFSessionId>'||
'<Sw:SnFOutputSeq>93749</Sw:SnFOutputSeq>'||
'<Sw:DeliveryTime>2003-07-19T08:58:37Z</Sw:DeliveryTime>'||
'</Sw:SnFOutputInfo>'||
'<SwInt:MRRResult>'||
'<SwInt:SNLId>SNL00835</SwInt:SNLId>'||
'<SwInt:SNLEP>funds_ep</SwInt:SNLEP>'||
'</SwInt:MRRResult>'||
'</SwInt:RequestDescriptor>'||
'<SwInt:RequestHeader>'||
'<SwInt:Requestor>cn=john-smith,ou=ny,o=bankdcba,o=swift</SwInt:Requestor>'||
'<SwInt:Responder>cn=mnop31,ou=zurich,o=bankwxyz,o=swift</SwInt:Responder>'||
'<SwInt:Service>swift.ifds!p</SwInt:Service>'||
'<SwInt:RequestRef>dcba-02241-235851-000972</SwInt:RequestRef>'||
'</SwInt:RequestHeader>'||
'<SwInt:RequestPayload>'||
'<Ah:AppHdr xmlns:Ah="urn:swift:xsd:$ahV10">'||
'<Ah:MsgRef>123456789</Ah:MsgRef>'||
'<Ah:CrDate>2002-09-09T17:12:31</Ah:CrDate>'||
'</Ah:AppHdr>'||
'</SwInt:RequestPayload>'||
'<SwSec:Crypto>'||
'<SwSec:CryptoControl>'||
'<SwSec:MemberRef>RequestPayload</SwSec:MemberRef>'||
'<SwSec:SignDN>cn=socsw16,ou=pclab,o=swhqbebb,o=swift</SwSec:SignDN>'||
'</SwSec:CryptoControl>'||
'</SwSec:Crypto>'||
'</SwInt:RequestHandle>'||
'</SwInt:HandleRequest>');

l_v varchar2(100);
begin

-- this one is OK dbms_output.put_line(l_req.extract('SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Requestor/text()').getStringVal());


-- extract when I have a namspace within a namespace gives
-- an ORA-31013: Invalid XPATH expression error
dbms_output.put_line(l_req.extract('SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestPayload/Ah:AppHdr/Ah:MsgRef/text()').getStringVal()) ;
end;

declare
*
ERROR at line 1:
ORA-31013: Invalid XPATH expression
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 50

thanks

and Tom said...

I asked Sean Dillon, our local XML technologist to take a look at this, here's what he had to say:
--

You need to provide the namespace information in the extract function, and currently there's no way in the extract function to specify nested namespaces so you need to use a nested-depth wildcard (//) to reach it.

dbms_output.put_line(
l_req.extract('//Ah:MsgRef/text()',
'xmlns:Ah="urn:swift:xsd:$ahV10"').getStringVal()
);

Hope that helps!

_smd_


Rating

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thank you

phil, November 24, 2004 - 4:20 pm UTC


You can use this to reach it

Darren L, November 24, 2004 - 4:55 pm UTC

ps use extractvalue and not extract otherwise ampersands will not be decoded etc...

extractvalue(l_req, '/*[namespace-uri()="urn:swift:snl:ns.SwInt" and local-name()="HandleRequest"]'||
'/*[namespace-uri()="urn:swift:snl:ns.SwInt" and local-name()="RequestHandle"]'||
'/*[namespace-uri()="urn:swift:snl:ns.SwInt" and local-name()="RequestPayload"]'||
'/*[namespace-uri()="urn:swift:xsd:$ahV10" and local-name()="AppHdr"]'||
'/*[namespace-uri()="urn:swift:xsd:$ahV10" and local-name()="MsgRef"]/text()')

i.e. specify the namespaces by name and not by shortcut (risky if someone changes the sortcut on you!) granted its a bit chatty :-) but it gets the answer and still works if someone alters xmlns:SwInt="urn:swift:snl:ns.SwInt" to xmlns:SomethingElse="urn:swift:snl:ns.SwInt" throughout.

Thank you

phil, November 25, 2004 - 8:19 pm UTC

Thank you for the above assistance.

is this the correct way for me to extract my values, when there are multiple iterations in different elememts. I get the answer that I can use and I expect. But is the plan "healthy" and the method correct? My eybrows arose at the the high cost and card.

My XML snippet ..

<SwInt:RequestHeader>
<SwInt:Requestor>cn=john-smith,ou=ny,o=bankdcba,o=swift</SwInt:Requestor>
<SwInt:Responder>cn=mnop31,ou=zurich,o=bankwxyz,o=swift</SwInt:Responder>
<SwInt:Service>swift.ifds!p</SwInt:Service>
<SwInt:RequestRef>dcba-02241-235851-000972</SwInt:RequestRef>
</SwInt:RequestHeader>
<SwInt:RequestPayload>
<Doc:Document xmlns:xsi="</code> http://www.w3.org/2001/XMLSchema-instance" <code>xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount">
<Doc:getaccount>
<Doc:NstrAcctSchCrit>
<Doc:AcctId>
<Doc:DmstAcct>ACCOUNT1</Doc:DmstAcct>
<Doc:DmstAcct>ACCOUNT2</Doc:DmstAcct>
</Doc:AcctId>
<Doc:Ccy>GBP</Doc:Ccy>
<Doc:Ccy>USD</Doc:Ccy>
<Doc:Ccy>AUD</Doc:Ccy>
</Doc:NstrAcctSchCrit>
</Doc:getaccount>
</SwInt:RequestPayload>

...


select
(substr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Responder/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),
instr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Responder/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),'o=',-1,2)+2,
instr(substr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Responder/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),
instr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Responder/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),'o=',-1,2)+2),',')-1)
)
Servicer,
(substr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Requestor/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),
instr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Requestor/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),'o=',-1,2)+2,
instr(substr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Requestor/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),
instr((extractvalue(swift_xml,'//SwInt:HandleRequest/SwInt:RequestHandle/SwInt:RequestHeader/SwInt:Requestor/text()','xmlns:SwInt="urn:swift:snl:ns.SwInt"')),'o=',-1,2)+2),',')-1)
)
Owner ,
extract(value(Acc), '//Doc:DmstAcct/text()','xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount"').getStringVal() Accout,
extract(value(Cur), '//Doc:Ccy/text()','xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount"').getStringVal() Currency
from n_sys.xml1,
table( xmlsequence( extract(swift_xml, '//Doc:AcctId/Doc:DmstAcct','xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount"') )) Acc,
table( xmlsequence( extract(swift_xml, '//Doc:NstrAcctSchCrit/Doc:Ccy','xmlns:Doc="urn:swift:xsd:swift.cashrepv1$getaccount"') )) Cur

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=440398966 Card=32697
6213824 Bytes=655914284930944)

1 0 NESTED LOOPS (Cost=440398966 Card=326976213824 Bytes=65591
4284930944)

2 1 NESTED LOOPS (Cost=53918 Card=40031368 Bytes=80222861472
)

3 2 TABLE ACCESS (FULL) OF 'XML1' (Cost=7 Card=4901 Bytes=
9811802)

4 2 COLLECTION ITERATOR (PICKLER FETCH) OF 'XMLSEQUENCEFRO
MXMLTYPE'

5 1 COLLECTION ITERATOR (PICKLER FETCH) OF 'XMLSEQUENCEFROMX
MLTYPE'





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
646 bytes sent via SQL*Net to client
605 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed


PS .... excellent effort by Oracle with reference/use of XML! !

getting the namespace

phil, January 06, 2005 - 12:32 am UTC

as a response to Sean in the original question ... and using the original xml

if you did not know the value of the namespace attribute xmlns:Ah ( xmlns:Ah="urn:swift:xsd:$ahV10" ) how would you then go about getting the attribute value so you can then perform extracts on that namespace.

(in my case the namespace attribute contains a version number, extracting this would then lead me to which ever sechema i need to validate against )

Tom Kyte
February 01, 2005 - 2:24 pm UTC

Hi Phil, Sean here -

Well, you can parse the document and extract the namespace from the document. That would be the official way to retrieve it. The unofficial way to retrieve it would be good ole' instr/substr :). I won't advocate using that approach, but it's one you could try. Parsing the document, however, is resource intensive and if you're parsing the document anyway, do you really need to use an extract to get an XPath value? (seeing as you'd have it (1) instantiated into a DOM tree, (2) have a SAX parser reading it already or (3) have the data streaming in a StAX parser) Just a thought.




result

phil, January 06, 2005 - 6:17 pm UTC

sorry ... easy really!

xmltype.extract(l_req,'//@xmlns:Doc').getStringVal()

phil

Why?

Mike, April 14, 2005 - 4:01 am UTC

I tried running Phil's script. In my case the first extract raises the error 'ORA-31013: Invalid XPATH expression' (and the second one as well). Can anyone tell me why the first extract statement is not working in my case? Im on a 9.2.0.5.0 DB.

Difference between 9.2.0.1.0 and 9.2.0.5.0??

Mike, April 21, 2005 - 5:04 am UTC

Hi,

I have some XML that has a namespace defined. I have not registered the XML schema in the database. I want to use the ExtractValue function but if I use this function on a 9.2.0.5.0 database it complains, if I use it on a 9.2.0.1.0 database it works without any problems. Below is a test script:

declare
l_xml xmltype := xmltype('<?xml version="1.0"?>
<Mutation_publication xmlns:DLHeader="</code> http://www.somewhere.nl/Std/V2/DLHeader.xsd"
xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="
http://somewhere/DLPublic/V2/therest.xsd" >
    <DLHeader>
        <DLHeader:Sender>
            <DLHeader:SystemID>THESYSTEM</DLHeader:SystemID>
            <DLHeader:ProcessID>THEPROCESS</DLHeader:ProcessID>
        </DLHeader:Sender>
        <DLHeader:Message>
            <DLHeader:DateTime>2004-10-01T11:35:14</DLHeader:DateTime>
            <DLHeader:MessageType>mutationPublication</DLHeader:MessageType>
        </DLHeader:Message>
        <DLHeader:Receiver/>
        <DLHeader:Authorisation/>
        <DLHeader:ReturnStatus/>
    </DLHeader>
</Mutation_publication>');

  l_str varchar2(100);

begin
  begin
    /* this statement works*/
    select extractvalue( l_xml
                       , '/Mutation_publication/DLHeader/DLHeader:Sender/DLHeader:SystemID'
                       , 'xmlns:DLHeader="
http://www.somewhere.nl/Std/V2/DLHeader.xsd"' <code>
)
into l_str
from dual;

dbms_output.put_line('Test 1: ['||l_str||']');
exception
when others then
dbms_output.put_line('Test 1 failed. Errormessage: '||sqlerrm);
end;

begin
/* this statement doesn't work*/
select extractvalue( l_xml
, '/Mutatie_publicatie_DLRelatie/DLHeader/DLHeader:Sender/DLHeader:SystemID'
)
into l_str
from dual;

dbms_output.put_line('test 2: ['||l_str||']');

exception
when others then
dbms_output.put_line('Test 2 failed. Errormessage: '||sqlerrm);
end;
end;

When executed on 9.2.0.1.0 it produces:
Test 1: [THESYSTEM]
Test 2: [THESYSTEM]

When executed on 9.2.0.5.0 it produces:
Test 1: [THESYSTEM]
Test 2 failed. Errormessage: ORA-31013: Invalid XPATH expression

I don't want to register the schema. Its a generic schema which holds loads of information of which i only use a small part. The schema is likley to change in the future and i dont want to re-register the schema every time even if there are changes that dont affect me.


Thanks.


Tom Kyte
April 25, 2005 - 10:16 am UTC

I asked Sean Dillon, my XML technologist, to take a look at this here's what he had to say:
--

Hi,

Running this on 9.2.0.1, 9.2.0.5 and 10.0.1.2 all worked fine for me?

scott@ORA9IR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

scott@ORA9IR2> declare
2 l_xml xmltype := xmltype('<?xml version="1.0"?>
3 <Mutation_publication xmlns:DLHeader="</code> http://www.somewhere.nl/Std/V2/DLHeader.xsd"
  4  xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
  5  xsi:noNamespaceSchemaLocation="
http://somewhere/DLPublic/V2/therest.xsd" > <code>
6 <DLHeader>
7 <DLHeader:Sender>
8 <DLHeader:SystemID>THESYSTEM</DLHeader:SystemID>
9 <DLHeader:ProcessID>THEPROCESS</DLHeader:ProcessID>
10 </DLHeader:Sender>
11 <DLHeader:Message>
12 <DLHeader:DateTime>2004-10-01T11:35:14</DLHeader:DateTime>
13 <DLHeader:MessageType>mutationPublication</DLHeader:MessageType>
14 </DLHeader:Message>
15 <DLHeader:Receiver/>
16 <DLHeader:Authorisation/>
17 <DLHeader:ReturnStatus/>
18 </DLHeader>
19 </Mutation_publication>');
20 begin
21 null;
22 end;
23 /

PL/SQL procedure successfully completed.

_smd_

I'm afraid you missed the point

Mike, April 26, 2005 - 9:33 am UTC

Hi,

Thanks for your quick response. I am, however, afraid you missed the point. The declaration/initialization of the xmltype variable is not the problem. The problem is the second 'select extractvalue ...' statement in the PL/SQL body, this raises the ORA-31013 error.

Kind regards,

Mike

Extractvalue()

Raj, October 20, 2005 - 5:47 pm UTC

Tom,

I'm having a problem with executing the right syntax. Here's a small reproducible test case.

Smalltest.xml
==================
<?xml version="1.0" encoding="ISO-8859-1"?>
<Snapshot Name="Current Version" xmlns:xlink="</code> http://www.w3.org/1999/xlink" > <code>
<Properties>
<Name>
<Value>Current Version</Value>
</Name>
</Properties>
<Content>
<Class Name="aWFLine">
<Properties>
<Name>
<Value>aWFLine</Value>
</Name>
</Properties>
<Variables>
<Variable Name="MyOwner">
<Properties>
<Name>
<Value>MyOwner</Value>
</Name>
<myType>
<ReferenceType Name="_RefOwner0aWFLine" Kind="aReftoType">
<Properties>
<Name>
<Value>_RefOwner0aWFLine</Value>
</Name>
<isOwner>
<Value>false</Value>
</isOwner>
<InTransaction>
<Value>false</Value>
</InTransaction>
<pointsToVersion>
<Value>false</Value>
</pointsToVersion>
<AcceptedClassDef>
<Value>aWFUserSignedItem</Value>
</AcceptedClassDef>
</Properties>
</ReferenceType>
</myType>
</Properties>
</Variable>
<Variable Name="Product">
<Properties>
<Name>
<Value>Product</Value>
</Name>
<myType>
<ReferenceType Name="_RefProduct0aWFLine" Kind="aReftoType">
<Properties>
<Name>
<Value>_RefProduct0aWFLine</Value>
</Name>
<isOwner>
<Value>false</Value>
</isOwner>
<InTransaction>
<Value>false</Value>
</InTransaction>
<pointsToVersion>
<Value>false</Value>
</pointsToVersion>
<AcceptedClassDef>
<Value>aWFDesc</Value>
</AcceptedClassDef>
</Properties>
</ReferenceType>
</myType>
</Properties>
</Variable>
</Variables>
</Class>
<Class Name="aWFQuantifiedLine">
<Properties>
<Name>
<Value>aWFQuantifiedLine</Value>
</Name>
</Properties>
<Variables>
<Variable Name="Quantity">
<Properties>
<Name>
<Value>Quantity</Value>
</Name>
<myType>
<Value>Int4</Value>
</myType>
</Properties>
</Variable>
</Variables>
</Class>
<Class Name="aWFValuedLine">
<Properties>
<Name>
<Value>aWFValuedLine</Value>
</Name>
</Properties>
<Variables>
<Variable Name="Value">
<Properties>
<Name>
<Value>Value</Value>
</Name>
<myType>
<Value>WFTypes.tWFAmount</Value>
</myType>
</Properties>
</Variable>
<OverrideVariable Name="Product" Kind="aOverrideInstanceVarDesc">
<Properties>
<Name>
<Value>Product</Value>
</Name>
<myType>
<ReferenceType Name="_RefProduct0aWFValuedLine" Kind="aReftoType">
<Properties>
<Name>
<Value>_RefProduct0aWFValuedLine</Value>
</Name>
<isOwner>
<Value>false</Value>
</isOwner>
<InTransaction>
<Value>false</Value>
</InTransaction>
<pointsToVersion>
<Value>false</Value>
</pointsToVersion>
<AcceptedClassDef>
<Value>aWFWithValueProductDesc</Value>
</AcceptedClassDef>
</Properties>
</ReferenceType>
</myType>
<OverridenVar>
<Value>aWFLine.Product</Value>
</OverridenVar>
</Properties>
</OverrideVariable>
</Variables>
</Class>
</Content>
</Snapshot>

eim@DEV.WORLD> drop table test_xmltable
2 /

Table dropped.

eim@DEV.WORLD> create table test_xmltable(doc_id number, xml_data xmltype)
2 /

Table created.

eim@DEV.WORLD> create or replace function GetDocument(p_filename in varchar2) return clob is
2 xbfile bfile;
3 xclob clob;
4 begin
5 xbfile := bfilename('XMLDIR', p_filename);
6 dbms_lob.open(xbfile);
7 dbms_lob.createtemporary(xclob, TRUE, dbms_lob.session);
8 dbms_lob.loadfromfile(xclob, xbfile, dbms_lob.getlength(xbfile));
9 dbms_lob.close(xbfile);
10 return xclob;
11 end GetDocument;
12 /

Function created.

eim@DEV.WORLD> insert into test_xmltable values(10, xmltype(getdocument('Smalltest.xml')))
2 /

1 row created.

eim@DEV.WORLD> commit
2 /

Commit complete.

eim@DEV.WORLD> select extractValue(value(w), '/Class/@Name') class_nm
2 from test_xmltable x,
3 table( xmlsequence(extract(x.xml_data,'/Snapshot/Content/Class'))) w
4 /

CLASS_NM
--------------------------------------------------------------------------------
aWFLine
aWFQuantifiedLine
aWFValuedLine

eim@DEV.WORLD> select trim(replace(extractValue(value(w), '/Properties/Name/Value/text()'), chr(10), '')) variable
2 from test_xmltable x,
3 table( xmlsequence(extract(x.xml_data,'/Snapshot/Content/Class/Variables/Variable/Properties'))) w
4 /

VARIABLE
--------------------------------------------------------------------------------
MyOwner
Product
Quantity
Value


I want to return all the variables for a given class. Something like this below.
How do I achieve it? I really need your help.

eim@DEV.WORLD> select extractValue(value(w), '/Class/@Name') class_nm,
2 trim(replace(extractValue(value(w), '/Class/Variables/Variable/Properties/Name/Value/text()'), chr(10), '')) variable
3 from test_xmltable x,
4 table( xmlsequence(extract(x.xml_data,'/Snapshot/Content/Class'))) w
5 where extractValue(value(w), '/Class/@Name') = 'aWFLine'
6 /
select extractValue(value(w), '/Class/@Name') class_nm,
*
ERROR at line 1:
ORA-19025: EXTRACTVALUE returns value of only one node



A reader, December 29, 2005 - 4:32 pm UTC

Hi Tom

I want to extract aop_id and aop from below SOAP documents and display it in following format

aop_id aop

19 General Litigation
39 Workers Compensation
50 Health Care Law

How can I do that using XMLSEQUENCE ?

Thanks

<?xml version="1.0" encoding="UTF-8" ?>
- <soapenv:Envelope xmlns:soapenv="</code> http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenc="
http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd="
http://www.w3.org/2001/XMLSchema"
xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance" >
- <soapenv:Body>
- <StatusResponse xmlns="
http://v1.types.crm.news.abc.com" > <code>
<status xmlns="">0</status>
- <aops xmlns="">
- <aop>
<aop_id>19</aop_id>
<aop_title>General Litigation</aop_title>
</aop>
+ <aop>
<aop_id>35</aop_id>
<aop_title>Bussiness Law</aop_title>
</aop>
- <aop>
<aop_id>39</aop_id>
<aop_title>Workers Compensation</aop_title>
</aop>
- <aop>
<aop_id>50</aop_id>
<aop_title>Health Care Law</aop_title>
</aop>
</aops>
</StatusResponse>
</soapenv:Body>
</soapenv:Envelope>






SOAP file reading

Nikhilesh, September 06, 2007 - 3:57 am UTC

Dear Tom,
Is it possibloe to read and write a SOAP file using Oracle 10g? If yes, could you please give a small example.
I'm new to 10g and SOAP.

Thanks in advance.
Tom Kyte
September 11, 2007 - 7:46 am UTC

SOAP is not "a file"

SOAP is the simple object access protocol

http://en.wikipedia.org/wiki/SOAP

not sure what your goal is, what you are actually trying to accomplish.

SOAP Extraction

Nikhilesh, September 12, 2007 - 2:17 am UTC

Dear Tom,
Thanks for your reply.
Actually we have a .xml file which contains SOAP tags
----------------------------------------------------------
e.g.<SOAP-ENV:Envelope xmlns:SOAP-ENV=" http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC=" http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance"
----------------------------------------------------------
What I need to do is treat this file as a simple xml file and extract the data from various nodes and store it in the database. Will oracle validate this file as XML file? Is it feasible.

Thanks in advance.

ORA-19025 on HTML Break tag

A reader, September 14, 2010 - 9:13 am UTC

Hi Tom,

We are trying to extract the value of an XML node in Oracle, and we are getting an unexpected ORA-19025 on an HTML break tag. Is there any reason why Oracle would treat an HTML line break as another XML node? Our fron-end development tool provides the expected result using the same XML and XPath, so we are wondering why Oracle is behaving differently...

Here's a test case for an issue we are encountering:

create table junk.test_html_break
(xmlcontent xmltype
);

Insert into junk.test_html_break (xmlcontent)
Values (XMLTYPE('<xml><TestNode>XML with HTML breaks<br/><br/>Why is this causing ORA-19025: EXTRACTVALUE returns value of only one node</TestNode></xml>'));

select a.xmlcontent
--,EXTRACTVALUE (a.xmlcontent, '//TestNode[1]') this_will_error
,EXTRACTVALUE (XMLTYPE(REPLACE(a.xmlcontent,'<br/>',NULL)), '//TestNode[1]') this_is_okay
FROM junk.test_html_break a
;


DB version:
BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


Thanks!
RMT
Tom Kyte
September 15, 2010 - 7:18 am UTC

XML doesn't permit special characters like that in the data - that is NOT html in xml, that is xml in xml.


You would need to escape that data to have well formed XML - the <> *means* something in XML.

&lt; &gt; - are what you need.


see
https://studio.tellme.com/general/xmlprimer.html

for a basic tutorial.

RE: ORA-19025 on HTML Break tag

A reader, September 15, 2010 - 9:10 am UTC

I think you are missing something. The BR html tag is in valid xml syntax, the tag self-closes.
<br/>
is the same as
<br></br>
Of course this violates extractvalue. From the docs:

The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node. The result must be a single node and be either a text node, attribute, or element. If the result is an element, then the element must have a single text node as its child, and it is this value that the function returns.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions061.htm#SQLRF06173

Try this instead:
select extract(XMLTYPE('<xml><TestNode>XML with HTML breaks<br/><br/>Why is this causing ORA-19025: EXTRACTVALUE returns value of only one node</TestNode></xml>'), '//TestNode[1]').getstringval() answer
from dual

Tom Kyte
September 15, 2010 - 9:41 am UTC

they are saying to me "we are storing html in xml", they do not want the br tags to be XML, they are html to them. They are storing HTML in XML fields - they need to escape that data.

I'm not missing anything - they do not have valid XML for what they are trying to store.

A reader, September 15, 2010 - 9:20 am UTC

Or of course
select 
  extractvalue(
    XMLTYPE('<xml><TestNode>XML with HTML breaks<br/><br/>Why is this causing ORA-19025: EXTRACTVALUE returns value of only one node</TestNode></xml>'), '//TestNode[1]/text()') answer
  from dual

RMT, September 16, 2010 - 2:24 pm UTC

Thank you both very much for the feedback.

The actual data is fed from an interface that we have with another application, and it was valid XML that is accepted by both our application and the Oracle database, so it is somewhat too late to "fix" the data.

At any rate, appending "/text()" to the XPath seems to resolve the issue on our end.

regards,
RMT
Tom Kyte
September 16, 2010 - 2:43 pm UTC

but if you want to call it HTML in the XML - it isn't. It is an xml section - <br></br> - an empty one - but it is XML - not html.

XMLType.method()

Anmol Kaushik, September 26, 2012 - 12:19 pm UTC

Hi Tom,
I came to know that Oracle 11G has deprecated functions like extract() and extractValue().
We are migrating from 9i and 10g to 11g and need to replace the deprecated functions. While code review I found out that we are using XMLType.extract("text()") function i.e. we are using the XMLtype API.
To be precise..
XMLAGG(<some statements>).extract('text()').getStringVal()

Does it mean, that we need to discontinue using this member function of the XMLType API?
If yes, could you let me know or guide me to a resource where I can know the equivalent of this member function extract?

Rajesh M, February 03, 2018 - 11:05 pm UTC

Dear Experts,
I'm facing some issue while reading the XML payload which contains nested nodes.
Not sure, if I'm doing some thing wrong, I'm not able to get the desired output. Please help me to correct:

here's my livesql script:

https://livesql.oracle.com/apex/livesql/file/content_F742EXBMSOIEU7IEC3IQYCXEI.html

run below statement:
EXEC test_xml_raj;

Expectation: it should print below.

l_cmilocation = P44-I44-P6-100

which is not happening, getting NO DATA FOUND.
Chris Saxon
February 05, 2018 - 11:22 am UTC

You need to qualify the path to your target elements starting from the root. In this part you're starting part way through!

FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml_source, 'cmilocation:GeographicalArea/cmilocation:AddressValidation'


Also make sure you pass the namespace definitions for all the namespaces you use.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here