Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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.

share|improve this answer
    
I can write something like ` SELECT replace(XMLSERIALIZE(CONTENT body as VARCHAR),'"','&quot;') from news; ` and so on. This is quite naive as well. It's a pity if there is no more handy language, since anything else related to XML output fits just perfect to me. –  shabunc Jan 13 '12 at 15:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.