How to XML-encode a string in PostgreSQL?

Question

Question: I can create a XML-encoded string in Postgres like this:

SELECT xmlelement(name name, 'AT&T', null )

now I want to get the xml encoded value, that is to say AT&T.

But if I do:

SELECT unnest(xpath('/name/text()', xmlelement(name name, 'AT&T', null )))

then I get AT&T, not AT&T.

How can I get the XML-encoded value?

Furthermore, isn't it possible to supply an empty name to xmlelement, and just cast to varchar?

Answer 1

I would suggest to use a simple function.

create or replace function xml_escape(s text) returns text as
$$
  select replace(replace(replace(s, '&', '&amp;'), '>', '&gt;'), '<', '&lt;');
$$
language sql immutable strict;