Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TRANSACTION_DATA_TABLE (
  ID NUMBER,
  TRAN_DATA XMLTYPE
);

INSERT INTO TRANSACTION_DATA_TABLE (ID, TRAN_DATA) VALUES (
  1,
  XMLTYPE('<TransactionData>
              <Request>
                <AppData>
                  <FirstName>John &amp; Freddy</FirstName>
                  <LastName>Doe</LastName>
                </AppData>
              </Request>
            </TransactionData>')
);
1 rows affected

SELECT tdt.ID,
       REGEXP_REPLACE(d.FirstName, '[&<>]', '') AS FirstName,
       REGEXP_REPLACE(d.LastName, '[&<>]', '') AS LastName
FROM TRANSACTION_DATA_TABLE tdt,
     XMLTABLE('/TransactionData/Request/AppData'
              PASSING tdt.TRAN_DATA
              COLUMNS
                FirstName VARCHAR2(200) PATH 'FirstName',
                LastName VARCHAR2(200) PATH 'LastName') as d;


ID
FIRSTNAME
LASTNAME


1
John  Freddy
Doe