XMLType Extract Distinct Value ID from a first node-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( '<?xml version="1.0"?> 
<ROWSET> 
<ROW> <PERSON_NUMBER>1000142</PERSON_NUMBER> <LOAN_1>25000</LOAN_1> <LOAN_2>26000</LOAN_2> </ROW> 
<ROW> <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;

CodePudding user response：

As I commented when you asked this as a follow-up to your previous question, use the XPATH:

/ROWSET/ROW[1]/PERSON_NUMBER/text()

Then:

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

Outputs:

P#

1000142

P#
1000142

If you did want to extract all the PERSON_NUMBER values and then find only the DISTINCT ones then you can use:

SELECT DISTINCT p#
from XMLTABLE(
       '/ROWSET/ROW'
       PASSING xmltype(
         '<?xml version="1.0"?> 
         <ROWSET> 
           <ROW>
             <PERSON_NUMBER>1000142</PERSON_NUMBER>
             <LOAN_1>25000</LOAN_1>
             <LOAN_2>26000</LOAN_2>
           </ROW> 
           <ROW>
             <PERSON_NUMBER>1000142</PERSON_NUMBER>
             <LOAN_1>25000</LOAN_1>
             <LOAN_2>26000</LOAN_2>
           </ROW> 
         </ROWSET>'
       )
       COLUMNS
         p# NUMBER PATH './PERSON_NUMBER'
     );

Which, for the sample data outputs the same as above.

db<>fiddle here

CodePudding user response：

You could use something like this as well:

WITH tbl AS
(
  SELECT XMLTYPE( 
                  '<?xml version="1.0"?> 
                  <ROWSET> 
                  <ROW> <PERSON_NUMBER>1000142</PERSON_NUMBER> <LOAN_1>25000</LOAN_1> <LOAN_2>26000</LOAN_2> </ROW> 
                  <ROW> <PERSON_NUMBER>1000142</PERSON_NUMBER> <LOAN_1>25000</LOAN_1> <LOAN_2>26000</LOAN_2> </ROW> 
                  </ROWSET>'    
                )  xmldata
    FROM dual
)
SELECT node_name, node_value
  FROM tbl
     , XMLTABLE('//*'
                PASSING tbl.xmldata
                COLUMNS node_name  VARCHAR2(100) path 'name()'
                      , node_value VARCHAR2(400) path 'text()')
 WHERE node_name = 'PERSON_NUMBER';

CodePudding user response：

If you only want the person number from the first row then your XPath should be:

'/ROWSET/ROW[1]/PERSON_NUMBER/text()'

instead of getting the first PERSON_NUMBER from every ROW.

If your XML can have several different person numbers, some or all of which are repeated, then get all of them with XMLTable, and then get the distinct values:

select distinct x.person_number
from xmltable(
  '/ROWSET/ROW'
  passing xmltype( '<?xml version="1.0"?> 
<ROWSET> 
<ROW> <PERSON_NUMBER>1000142</PERSON_NUMBER> <LOAN_1>25000</LOAN_1> <LOAN_2>26000</LOAN_2> </ROW> 
<ROW> <PERSON_NUMBER>1000142</PERSON_NUMBER> <LOAN_1>25000</LOAN_1> <LOAN_2>26000</LOAN_2> </ROW> 
</ROWSET>')
  columns person_number number path 'PERSON_NUMBER'
 ) x

db<>fiddle