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 <
>
to >
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, '<', '<')
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.