How can I escape XML field in XML-output query?

Posted on

Question :

I have following request:

SELECT XMLAGG(data) FROM
(SELECT XMLELEMENT(name item, XMLFOREST(
   title,
   body as description,
   date_created as date,
   'http://example.com/news'||id as link 
)) AS data FROM NEWS) AS smthng;

The issue is that body is actually XML itself, so the question is –
How can I escape XML field (changing < to &lt; > to &gt; etc.) in this query?

Answer :

The naive approach would be to wrap your query around another SELECT and use replace.

SELECT replace(
    (SELECT XMLAGG(data) FROM
         (SELECT XMLELEMENT(name item, XMLFOREST(
          title,
          body as description,
          date_created as date,
          'http://example.com/news'||id as link 
         )
     ) AS data FROM NEWS) AS smthng)::text, '<', '&lt;')

And do the same for >. A easier and more elgant mthod would be to to it with the language that should deliver/process/… the XML.

Leave a Reply

Your email address will not be published. Required fields are marked *