I have a XML data stored in a CLOB
column and I would like to delete some nodes based on a specific condition.
Example XML Data :
<?xml version="1.0" encoding="UTF-8"?>
<payment>
<person>
<surname>Marco</surname>
<name>Gralike</name>
<salary>2345</salary>
</person>
<person>
<surname>ABC</surname>
<name>TEST</name>
<salary>1234</salary>
<person>
<surname>Tiger</surname>
<name>Scott</name>
<salary>2222</salary>
</person>
</person>
</payment>
<payment>
<person>
<surname>BertJan</surname>
<name>Meinders</name>
<salary>3456</salary>
<salary>125</salary>
</person>
<person>
<surname>XYZ</surname>
<name>TEST</name>
<salary>1234</salary>
</person>
</payment>
<payment>
<person>
<surname>Chris</surname>
<name>Gralike</name>
<salary>4567</salary>
</person>
<person>
<surname>LMN</surname>
<name>TEST</name>
<salary>1234</salary>
</person>
</payment>
I need a Oracle PLSQL script to delete all the person tags if it contains TEST.
Final output would be :
<?xml version="1.0" encoding="UTF-8"?>
<payment>
<person>
<surname>Marco</surname>
<name>Gralike</name>
<salary>2345</salary>
</person>
</payment>
<payment>
<person>
<surname>BertJan</surname>
<name>Meinders</name>
<salary>3456</salary>
<salary>125</salary>
</person>
</payment>
<payment>
<person>
<surname>Chris</surname>
<name>Gralike</name>
<salary>4567</salary>
</person>
</payment>
Thanks in advance.
Your provided XML doesn't have a root and can't be parsed by XML parser.
Assuming it does (say
payments
) as shown below:You can use this:
Produces:
EDIT:
If you want to delete a node that doesn't have a given child, use this:
It'll delete all the payment tags that don't have a person in it.