CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.1postgresql-9.2
Stefan Steiger
Stefan Steiger

Reputation: 82296

How to XML-encode a string in PostgreSQL?

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?

Upvotes: 3

Views: 2293

Answers (3)

DYMATEJlb
DYMATEJlb

Reputation: 97

CREATE OR REPLACE FUNCTION encode_xml_escape(s TEXT) RETURNS TEXT AS $$
DECLARE
    TempString TEXT;
BEGIN
    TempString := REPLACE(s, '&', '&');
    TempString := REPLACE(TempString, '"', '"');
    TempString := REPLACE(TempString, '''', ''');
    TempString := REPLACE(TempString, '<', '&lt;');
    TempString := REPLACE(TempString, '>', '&gt;');
    RETURN TempString;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION decode_xml_escape(s TEXT) RETURNS TEXT AS $$
DECLARE
    TempString TEXT;
BEGIN
    TempString := REPLACE(s, '&quot;', '"');
    TempString := REPLACE(TempString, '&apos;', '''');
    TempString := REPLACE(TempString, '&lt;', '<');
    TempString := REPLACE(TempString, '&gt;', '>');
    TempString := REPLACE(TempString, '&amp;', '&');
    RETURN TempString;
END;
$$ LANGUAGE plpgsql;

Upvotes: 0

Stefanov.sm
Stefanov.sm

Reputation: 13049

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;

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

If you are writing to an HTML client then you will have to HTML escape that for it to show the raw HTML.

As I see you are mainly a C# developer then the static method HttpUtility.HtmlEncode() will do it.

Upvotes: 1

Related Questions