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

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 )

sorry ... easy really!

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

phil

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.

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.

  4  xmlns:xsi="

  5  xsi:noNamespaceSchemaLocation="

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

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

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>

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.

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.

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

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

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

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

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?

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.

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