I am doing the following:
DECLARE
xmlvalue CLOB
:= '<HTNG_HotelRoomStatusSearchRS><Success></Success><Errors><Error Type="0" ShortText="None"></Error></Errors>
<RoomInformationList><TPA_Extensions><TPA_Extension>
<UNITI></UNITI></TPA_Extension></TPA_Extensions>
</RoomInformationList></HTNG_HotelRoomStatusSearchRS>';
x_xml XMLTYPE;
newvalue VARCHAR2 (2000) := '<UNIT UNIT_NUM="ABCD" />';
BEGIN
x_xml := xmltype (xmlvalue);
FOR i IN (SELECT col1
FROM xml_modify
)
LOOP
SELECT XMLQUERY ('copy $i := $p1 modify
(for $j in $i//TPA_Extensions/TPA_Extension/UNITI
return insert node $p2 before $j)
return $i' PASSING x_xml AS "p1", xmltype (i.col1) AS "p2" RETURNING CONTENT)
INTO x_xml
FROM DUAL;
END LOOP;
x_xml := x_xml.DELETEXML ('//UNITI');
DBMS_OUTPUT.put_line (x_xml.getstringval ());
END;