The following examples are applied on Oracle DB, but they can be used also with another language, e.g. Java.
The last examples illustrate in particular SOAP envelope xml extraction.
Simple Examles:
select xmltype('<a><b><return>vag</return></b></a>').extract('/a/b/text()')
.getstringval() final_extract from dual
--FINAL_EXTRACT: <null>
select xmltype('<a><b><return>vag</return></b></a>').extract('/a/b/child::node()')
.getstringval() final_extract from dual
--FINAL_EXTRACT: <return>vag</return>
select xmltype('<a><b><return>vag</return></b></a>').extract('//b/child::node()')
.getstringval() final_extract from dual
--FINAL_EXTRACT: <return>vag</return>
select xmltype('<a><b><return>vag</return></b></a>')
.extract('//return/text()').getstringval() final_extract from dual
--FINAL_EXTRACT: vag
select xmltype('<a><b><return>vag</return></b></a>').
extract('/a/b/child::node()/text()').getstringval() final_extract from dual
--FINAL_EXTRACT: vag
Simple examples with namespace (be careful, it is case sensitive):
select xmltype('<s:a xmlns:s="qw"><s:b><return>vag</return></s:b></s:a>').
extract('/s:a/s:b/text()','xmlns:s="qw"').getstringval() final_extract from dual
--FINAL_EXTRACT: <null>
select xmltype('<s:a xmlns:s="qw"><s:b><return>vag</return></s:b></s:a>').
extract('/s:a/s:b/child::node()','xmlns:s="qw"').getstringval() final_extract from dual
--FINAL_EXTRACT: <return>vag</return>
Complicated example with namespace:
select xmltype('<?xml version="1.0"?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:getTimeAsStringResponse xmlns:ns2="http://ts.ch01/">
<return>Sat Dec 14 22:40:49 CET 2013</return>
</ns2:getTimeAsStringResponse>
</S:Body>
</S:Envelope>')
.extract('/S:Envelope/S:Body/child::node()',
'xmlns:S="http://schemas.xmlsoap.org/soap/envelope/"').getstringval() final_extract from dual
--FINAL_EXTRACT: <ns2:getTimeAsStringResponse xmlns:ns2="http://ts.ch01/">
--<return>Sat Dec 14 22:40:49 CET 2013</return>
--</ns2:getTimeAsStringResponse>
select xmltype('<?xml version="1.0" ?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:getTimeAsStringResponse xmlns:ns2="http://ts.ch01/">
<return>Sat Dec 14 22:40:49 CET 2013</return>
</ns2:getTimeAsStringResponse>
</S:Body>
</S:Envelope>')
.extract('/S:Envelope/S:Body/child::node()/return/text()',
'xmlns:S="http://schemas.xmlsoap.org/soap/envelope/"').getstringval() final_extract from dual
--FINAL_EXTRACT: Sat Dec 14 22:40:49 CET 2013
select xmltype('<?xml version="1.0" ?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:getTimeAsStringResponse xmlns:ns2="http://ts.ch01/">
<return>Sat Dec 14 22:40:49 CET 2013</return>
</ns2:getTimeAsStringResponse>
</S:Body>
</S:Envelope>')
.extract('/S:Envelope/S:Body/child::node()/child::node()/text()',
'xmlns:S="http://schemas.xmlsoap.org/soap/envelope/"').getstringval() final_extract from dual
--FINAL_EXTRACT: Sat Dec 14 22:40:49 CET 2013
More complicated example with 2 namespaces:
select xmltype('<?xml version="1.0" ?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>
<ns2:getTimeAsStringResponse xmlns:ns2="http://ts.ch01/">
<return>Sat Dec 14 22:40:49 CET 2013</return>
</ns2:getTimeAsStringResponse>
</S:Body>
</S:Envelope>')
.extract('/S:Envelope/S:Body/ns2:getTimeAsStringResponse/child::node()/text()',
'xmlns:S="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns2="http://ts.ch01/"')
.getstringval() final_extract from dual
--FINAL_EXTRACT: Sat Dec 14 22:40:49 CET 2013
Examples of extracting value under specific property:
SELECT
xmltype('<?xml version="1.0" ?>
<myNS1:Envelope xmlns:myNS1="http://vag1/">
<myNS1:Body>
<myNS2:amount xmlns:myNS2="http://vag2/">
<myNS2:trans categ="WITHDRAW"><total>120</total></myNS2:trans>
<myNS2:trans categ="DEPOSIT"><total>65</total></myNS2:trans>
</myNS2:amount>
</myNS1:Body>
</myNS1:Envelope>').extract('/myNS1:Envelope/myNS1:Body/myNS2:amount/myNS2:trans[@categ="WITHDRAW"]/total/text()',
'xmlns:myNS1="http://vag1/" xmlns:myNS2="http://vag2/"').getstringval() final_extract from DUAL
--FINAL_EXTRACT: 120
SELECT
xmltype('<?xml version="1.0" ?>
<myNS1:Envelope xmlns:myNS1="http://vag1/">
<myNS1:Body>
<myNS2:amount xmlns:myNS2="http://vag2/">
<myNS2:trans><categ>WITHDRAW<total>120</total></categ></myNS2:trans>
<myNS2:trans><categ>DEPOSIT<total>65</total></categ></myNS2:trans>
</myNS2:amount>
</myNS1:Body>
</myNS1:Envelope>').extract('/myNS1:Envelope/myNS1:Body/myNS2:amount/myNS2:trans[categ/text()="WITHDRAW"]/categ/total/text()',
'xmlns:myNS1="http://vag1/" xmlns:myNS2="http://vag2/"').getstringval() final_extract from DUAL
--FINAL_EXTRACT: 120
SELECT
xmltype('<?xml version="1.0" ?>
<myNS1:Envelope xmlns:myNS1="http://vag1/">
<myNS1:Body>
<myNS2:amount xmlns:myNS2="http://vag2/">
<myNS2:trans categ="WITHDRAW"><total>120</total></myNS2:trans>
<myNS2:trans categ="DEPOSIT"><total>65</total></myNS2:trans>
</myNS2:amount>
</myNS1:Body>
</myNS1:Envelope>').extract('/myNS1:Envelope/myNS1:Body/myNS2:amount/myNS2:trans[2]/total/text()',
'xmlns:myNS1="http://vag1/" xmlns:myNS2="http://vag2/"').getstringval() final_extract from DUAL
--FINAL_EXTRACT: 65
Examples with XMLSEQUENCE:
select * from table(xmlsequence(EXTRACT(xmltype(
'<something>
<return><a>13</a></return>
<return><a>25</a></return>
</something>'
), '/something/return')))
--FINAL EXTRACT:
COLUMN_VALUE
-----------------
<return><a>13</a></return>
<return><a>25</a></return>
with capitals as (
select 1 country_id, 'Athens' capital from dual
union
select 2 country_id, 'Belgrade' capital from dual
)
select
extractvalue(column_value,'/country/@id') id,
extractvalue(column_value,'/country/@descr') descr,
extractvalue(column_value,'/country/text()') dial_code,
cp.capital
from
TABLE(XMLSEQUENCE(EXTRACT(xmltype(
'<Results>
<country id="1" descr="Greece">0030</country>
<country id="2" descr="Serbia">00381</country>
</Results>'), '/Results/country'))) ids,
capitals cp
where 1=1
and cp.country_id=extractvalue(column_value,'/country/@id')
--RESULT:
ID DESCR DIAL_CODE CAPITAL
1 Greece 0030 Athens
2 Serbia 00381 Belgrade