Extract specific xml node from duplicate ones in oracle

Question

Given is the value of "my_xml" column in "XYZ" table

<?xml version="1.0" encoding="UTF-8"?>
<India>
  <city>
    <string>ADI</string>
    <string>Ahmedabad</string>
  </city>
  <city>
    <string>GNR</string>
    <string>Gandhinagar</string>
  </city>
  <city>
    <string>PUN</string>
    <string>Pune</string>
  </city>
  <city>
    <string>RJT</string>
    <string>Rajkot</string>
  </city>
</India>

I am trying to extract value of second string node where first string node value is ADI

Output should be "Ahmedabad" only

Failed attempts:

select t.my_xml.extract('/India/city/string[2]/text()').getStringVal() from XYZ t where t.my_xml.existsNode('/India/city[string[1] = "ADI"]') = 1;

Output for above query is AhmedabadGandhinagarPuneRajkot

Expected output: Ahmedabad

How to extract specific node value for string node here?

score 2 · Accepted Answer · answered Jan 10 '17 at 13:10

You want to select the node that has the ADI text as first string.

Try this:

select 
    t.my_xml.extract('//city[./string[1]/text() = "ADI"]/string[2]/text()').getStringVal()
from XYZ t
    where t.my_xml.existsNode('/India/city[string[1] = "ADI"]') = 1;

score 2 · Answer 2 · answered Jan 10 '17 at 13:28

2

Use XMLTable to extract the values:

SELECT t.*
FROM   XYZ x,
       XMLTable(
         '/India/city'
         PASSING x.my_xml
         COLUMNS string1 CHAR(3)      PATH './string[1]',
                 string2 VARCHAR2(20) PATH './string[2]'
       ) t
WHERE  t.string1 = 'ADI';

answered Jan 10 '17 at 13:28

MT0

143,790
11
59
117

Just for my curiosity, any advantage of using XMLTable over extract() ? – Not a bug Jan 10 '17 at 13:38
2

@Kishan [`EXTRACT()`](https://docs.oracle.com/database/121/SQLRF/functions068.htm#SQLRF00640) and [`EXTRACTVALUE()`](https://docs.oracle.com/database/121/SQLRF/functions069.htm#SQLRF06173) are both deprecated in Oracle 12c (but included for backwards compatibility) - Oracle recommends using `XMLTable` instead. – MT0 Jan 10 '17 at 13:43
1

@Kishan apart from that if you want multiple values then you only need to call `XMLTable` once - compare that to the `EXTRACTVALUE` query where it is called once in the `SELECT` clause and then you need to call it a second time (or `EXISTSNODE`) in the `WHERE` clause. – MT0 Jan 10 '17 at 13:46
My concern is size of string1 and string2 is not fix (given by external user). Any alternative of passing size while column declaration? – Not a bug Jan 10 '17 at 13:50
1

@Kishan Use `VARCHAR2(4000)` or some sufficiently large value instead. Or you can go back to the external user and get the specification for the data exchange format being used. – MT0 Jan 10 '17 at 13:52
2

@Kishan Oracle's specification for [`XMLTABLE`](https://docs.oracle.com/database/121/SQLRF/functions269.htm#SQLRF06232) makes it appear that the datatype is mandatory but it appears to work without the datatype - so you could just omit it. – MT0 Jan 10 '17 at 14:01

score 0 · Answer 3 · answered Jan 10 '17 at 13:37

1) With xmlquery and flwor xquery

select xmlcast( xmlquery('for $i in ./India/city where $i//string[1]/text() = $cond return $i/string[2]/text()' passing xmltype(q'~<India>
  <city>
    <string>ADI</string>
    <string>Ahmedabad</string>
  </city>
  <city>
    <string>GNR</string>
    <string>Gandhinagar</string>
  </city>
  <city>
    <string>PUN</string>
    <string>Pune</string>
  </city>
  <city>
    <string>RJT</string>
    <string>Rajkot</string>
  </city>
</India>~'), 'ADI' as "cond"    returning content) as varchar2(20)) result  from dual;

2) If you're expecting more than one row try xmltable.

select *  from xmltable('$doc/India/city' passing xmltype(q'~<India>
  <city>
    <string>ADI</string>
    <string>Ahmedabad</string>
  </city>
  <city>
    <string>GNR</string>
    <string>Gandhinagar</string>
  </city>
  <city>
    <string>PUN</string>
    <string>Pune</string>
  </city>
   <city>
    <string>ADI</string>
    <string>Ahmedabad</string>
  </city>
  <city>
    <string>RJT</string>
    <string>Rajkot</string>
  </city>
</India>~') as "doc" 
columns 
   string1 varchar2(20) path'./string[1]/text()'
   , string2 varchar2(20) path'./string[2]/text()'

)
where string1='ADI'

and xmltable with flwor xquery

select column_value  from xmltable('for $el in $doc/India/city where $el//string[1]/text() = "ADI" return $el/string[2]/text()' passing xmltype(q'~<India>
  <city>
    <string>ADI</string>
    <string>Ahmedabad</string>
  </city>
  <city>
    <string>GNR</string>
    <string>Gandhinagar</string>
  </city>
  <city>
    <string>PUN</string>
    <string>Pune</string>
  </city>
   <city>
    <string>ADI</string>
    <string>Ahmedabad</string>
  </city>
  <city>
    <string>RJT</string>
    <string>Rajkot</string>
  </city>
</India>~') as "doc" )
;

Extract specific xml node from duplicate ones in oracle

3 Answers3