Amit - in your original question, you had an issue with the apostrophe. That can be addressed more easily. (As an aside, Oracle escapes apostrophe only when it stores the data, as in your example; if the data is generated on the fly, in a query, then the escaping doesn't happen. Similar to another XML question on AskTom just in the last few days!)
But now you show an example with ampersand. That can't work the way you are trying, and it's not a matter of extracting the data (which should be done with XMLTABLE); rather, your input string is not a valid XML document, so if you apply XMLTYPE to it you'll get an error already. The code you posted in your latest reply errored out for me; are you saying that it ran without error for you, and it only produced the unexpected result? Honestly, I don't understand how that is possible. You have an unescaped ampersand in the text content of a tag. That is invalid XML, and any compliant XML processor should throw an error. (Oracle does!). The ampersand should be represented by & in your input text.
Your question should be, "how do I create a valid XML document to be saved in an XMLTYPE column, so that the data can be extracted from it later." Presumably your user doesn't type an XML document; they type the employer, the address components etc. separately, and you are collecting everything into an XML document, a step you didn't show. That shouldn't be done by simply concatenating hard-coded text with the user's inputs. Instead, proper XML tools for generating XML documents should be used, just the same as the proper XML tools should be used to retrieve the data from the document. Then your user can type ampersand and such, without worrying about XML; and you can generate valid XML documents, from which the data can be extracted later.
You need to take a look at the XML generating tools of Oracle; you may want to start with XMLELEMENT, and expand from there. Specifically regarding your concern about escaping special characters: The XMLELEMENT function has the NOENTITYESCAPING option, which will allow you to create an element like <t>a'bc</t> (instead of <t>a'bc</t>, and it will even allow you to create an element <t>a&bc</t>. But if you do the latter, you will get an invalid XML document, just like the one in your code in the latest post. Oracle allows you to do this, assuming that you are taking responsibility for generating valid XML content; but whoever will try to use such a document later will get errors.
Here is a short illustration of both sides of the process. Note that I "insert" something in table A; in your example, you were "updating", which didn't make a lot of sense to me. (I understand you were using that as an illustration, of course.)
drop table tbl purge;
drop table a purge;
create table tbl (xdoc_id number primary key, xdoc xmltype);
create table a (employer_name varchar2(40), employer_address varchar2(40));
declare
e_name varchar2(40) := 'McDonald''s';
e_addr varchar2(40) := 'Main & Post';
x_doc xmltype;
begin
select xmlelement("Doc", xmlelement("EmployerName", e_name),
xmlelement("EmployerAddress", e_addr)
)
into x_doc
from dual
;
insert into tbl (xdoc_id, xdoc) values (1, x_doc);
insert into a (employer_name, employer_address)
select emp_name, emp_addr
from tbl,
xmltable('/Doc' passing xdoc
columns emp_name varchar2(40) path 'EmployerName',
emp_addr varchar2(40) path 'EmployerAddress'
)
where xdoc_id = 1
;
end;
/Now here's what is in the two tables; notice that the XML document has the entities (escaped characters), but table A shows the values as intended, with the original characters. No manipulation of the characters occurred either on generating the data or on retrieving it; everything is handled by the XML tools on both sides.
set long 200 -- so XDOC is shown in full
select * from tbl;
XDOC_ID XDOC
---------- -------------------------------------------------------
1 <Doc>
<EmployerName>McDonald's</EmployerName>
<EmployerAddress>Main & Post</EmployerAddress>
</Doc>
select * from a;
EMPLOYER_NAME EMPLOYER_ADDRESS
-------------------------------- --------------------------------
McDonald's Main & Post