Blog

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('vag').extract('/a/b/text()')
.getstringval() final_extract from dual
--FINAL_EXTRACT: 

select xmltype('vag').extract('/a/b/child::node()')
.getstringval() final_extract from dual
--FINAL_EXTRACT: vag

select xmltype('vag').extract('//b/child::node()')
.getstringval() final_extract from dual
--FINAL_EXTRACT: vag

select xmltype('vag')
.extract('//return/text()').getstringval() final_extract from dual
--FINAL_EXTRACT: vag

select xmltype('vag').
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('vag').
extract('/s:a/s:b/text()','xmlns:s="qw"').getstringval() final_extract from dual
--FINAL_EXTRACT: 

select xmltype('vag').
extract('/s:a/s:b/child::node()','xmlns:s="qw"').getstringval() final_extract from dual
--FINAL_EXTRACT: vag

Complicated example with namespace:

select xmltype('



Sat Dec 14 22:40:49 CET 2013


')
.extract('/S:Envelope/S:Body/child::node()',
'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('



Sat Dec 14 22:40:49 CET 2013


')
.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('



Sat Dec 14 22:40:49 CET 2013


')
.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('



Sat Dec 14 22:40:49 CET 2013


')
.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('



120
65


').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('



WITHDRAW120
DEPOSIT65


').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('



120
65


').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(
'
13
25
'
), '/something/return')))
--FINAL EXTRACT:
COLUMN_VALUE
-----------------
13
25

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(
'
0030
00381
'), '/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