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?

SELECT version(); --> "PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 32-bit" in pgAdmin3 Version 1.12.3 (Apr. 15 2011 REL-1_12_3) — Stefan Steiger
– Stefan Steiger, Commented Jan 9, 2013 at 16:00

Stefanov.sm · Accepted Answer · 2020-02-03 07:09:09Z

4

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;

answered Feb 3, 2020 at 7:09

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Clodoaldo Neto · Accepted Answer · 2013-01-09 13:21:51Z

1

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.

edited Jan 9, 2013 at 13:21

answered Jan 9, 2013 at 13:14

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

1 Comment

Clodoaldo Neto Over a year ago

@Quandary I don't understand your comment.

DYMATEJlb · Accepted Answer · 2024-10-30 08:13:41Z

CREATE OR REPLACE FUNCTION encode_xml_escape(s TEXT) RETURNS TEXT AS $$
DECLARE
    TempString TEXT;
BEGIN
    TempString := REPLACE(s, '&', '&amp;');
    TempString := REPLACE(TempString, '"', '&quot;');
    TempString := REPLACE(TempString, '''', '&apos;');
    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;

Collectives™ on Stack Overflow

How to XML-encode a string in PostgreSQL?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related