XMLType Extract Unique ID from Single Cell-CodePudding

Can you please help me out to fix the following? I only required a person_number once eg. 1000142 but I'm getting 10001421000142 like this.

because the values i have in xml cell have repeating number, so I only want to extract one unique person number.

select xmltype('

<PERSON_NUMBER>1000142</PERSON_NUMBER> <LOAN_1>25000</LOAN_1> <LOAN_2>26000</LOAN_2>

').extract( '//PERSON_NUMBER/text()' ).getstringval() p# from dual;

CodePudding user response：

Based on your initial version of the question (where the XML is a valid with a single root element rather than a forest of elements), you can use:

select xmltype(
  '<?xml version="1.0"?>
  <ROWSET>
    <ROW>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <LOAN_1>25000</LOAN_1>
      <LOAN_2>26000</LOAN_2>
    </ROW>
  </ROWSET>').extract( '//PERSON_NUMBER[1]/text()' ) .getstringval() p#
from dual;

Which outputs:

P#

1000142

P#
1000142

db<>fiddle here