How to delete more than one node with xml-query-CodePudding

with this code, I can remove one node (field_without_interest) of this xml element. But I would like to remove two nodes now( field_without_interest and PERSON_NUMBER). I have tried to place the for loop at differents places (You can see that in code. But I don't think it useful to for to write every attemps in stackoverflow) but to no avail.

SELECT XMLQUERY(
         'copy $NEWHTML := .
         modify (
           for $i in $NEWHTML/ROWSET/ROW/field_without_interest
           --for $j in $NEWHTML/ROWSET/ROW/PERSON_NUMBER
           return delete node $i--, node $j
         )
         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

code

CodePudding user response：

You may specify path alternatives in the modify clause to select nodes to delete:

SELECT XMLQUERY(
  'copy $NEWHTML := .
  modify (
    delete nodes
    $NEWHTML/ROWSET/ROW/*[
      self::field_without_interest
      or self::PERSON_NUMBER
    ]
  )
  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

| INTERESTED_ELEMENTS                                                                                      |
| :------------------------------------------------------------------------------------------------------- |
| <?xml version="1.0"?><ROWSET><ROW><LOAN_1>25000</LOAN_1></ROW><ROW><LOAN_1>25000</LOAN_1></ROW></ROWSET> |

db<>fiddle here