with this query I take everything inside the tag ROW. But I would like to take everything expect the tag <field_without_interest>.

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

I can select the sub tags

select xmltype(
  '<?xml version="1.0"?> 
  <ROWSET> 
    <ROW>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <LOAN_1>25000</LOAN_1>
      <field_without_interest>f</field_without_interest>
    </ROW> 
    <ROW>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <LOAN_1>25000</LOAN_1>
    </ROW> 
  </ROWSET>'
).extract( '/ROWSET/ROW/PERSON_NUMBER' ) .getstringval() p#
from dual;

But I'm selectioning only the half the tags that I am interested in.

Is there a way to write extract( '/ROWSET/ROW/PERSON_NUMBER' AND '/ROWSET/ROW/LOAN_1' ) Or extract( '/ROWSET/ROW/ except'/ROWSET/ROW/field_without_interest' )

code

CodePudding user response：

You can use XMLQUERY and FLOWR.

If you only want the first ROW then:

SELECT XMLQUERY(
         'copy $NEWHTML := /ROWSET/ROW[1]
         modify (
           for $i in $NEWHTML/field_without_interest
           return delete node $i
         )
         return $NEWHTML/*'
         PASSING xmltype(
  '<?xml version="1.0"?> 
  <ROWSET> 
    <ROW>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <LOAN_1>25000</LOAN_1>
      <field_without_interest>f</field_without_interest>
    </ROW> 
    <ROW>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <LOAN_1>25000</LOAN_1>
    </ROW> 
  </ROWSET>'
)
         RETURNING CONTENT
       ).getStringVal() AS interested_elements
FROM   DUAL;

Which outputs:

INTERESTED_ELEMENTS
<PERSON_NUMBER>1000142</PERSON_NUMBER><LOAN_1>25000</LOAN_1>

Or, if you want the entire ROWSET, you can use:

SELECT XMLQUERY(
         'copy $NEWHTML := .
         modify (
           for $i in $NEWHTML/ROWSET/ROW/field_without_interest
           return delete node $i
         )
         return $NEWHTML'
         PASSING xmltype(
  '<?xml version="1.0"?> 
  <ROWSET> 
    <ROW>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <LOAN_1>25000</LOAN_1>
      <field_without_interest>f</field_without_interest>
    </ROW> 
    <ROW>
      <PERSON_NUMBER>1000142</PERSON_NUMBER>
      <LOAN_1>25000</LOAN_1>
    </ROW> 
  </ROWSET>'
)
         RETURNING CONTENT
       ).getStringVal() AS interested_elements
FROM   DUAL;

Which outputs:

INTERESTED_ELEMENTS
<?xml version="1.0"?><ROWSET><ROW><PERSON_NUMBER>1000142</PERSON_NUMBER><LOAN_1>25000</LOAN_1></ROW><ROW><PERSON_NUMBER>1000142</PERSON_NUMBER><LOAN_1>25000</LOAN_1></ROW></ROWSET>

db<>fiddle here