Postgres Data to XML

Question

Does anyone know any good methods for converting database entries into XML?

I.e. if i have a table named "Users" with fields "first_name", "age", "last_name", I'd like to convert the table to:

<Users>
  <first_name>Papa</first_name>
  <age>50</age>
  <last_name>John</last_name>
</Users>

score 25 · Answer 1 · answered Aug 24 '10 at 19:08

25

In PostgreSQL you could it like this:

SELECT table_to_xml('users', true, false, '');

Or

SELECT query_to_xml('SELECT * FROM users', true, false, '');

There are other options as well, just check the manual.

answered Aug 24 '10 at 19:08

Frank Heikens

where will be the generated xml file location? Is there any default path exists? – Haji Jun 30 '14 at 11:56
1

@Haji There is no file location, just a result set from this query. You could use COPY to dump the file somewhere, COPY takes the filename and location as an input parameter. – Frank Heikens Jun 30 '14 at 19:35
2

Hi, it's been a while but what does do **true,false,''** ? Also when I **COPY** this output, there are a lot of **\n** chars. How can I remove these **\n** chars? – Yavuz Selim Dec 07 '16 at 22:19
Can you also do query_to_xml on a CTE? – wolf97084 May 22 '19 at 13:33
To use xml feature in postgres, Postgres server must be first compiled with libxml library. – Atul Soni Apr 08 '20 at 14:08

score 1 · Answer 2 · edited Apr 04 '12 at 18:02

1

This is a question independent of the DB it can be done with any DB supported by ActiveRecord.

User.find(some_id).to_xml(:except => [:id,:created_at,:updated_at])

The :except => [:id,:created_at,:updated_at] removes the Rails default columns from the XML output.

edited Apr 04 '12 at 18:02

szeryf

answered Aug 24 '10 at 18:52

jigfox

Thanks for the quick answer Jigfox. I probably shouldn't have tagged Rails with this questions. But what if, Rails isn't installed in the system? – Tian Aug 24 '10 at 19:00

2 Answers2