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>

Frank Heikens · Accepted Answer · 2010-08-24 19:08:13Z

26

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, 2010 at 19:08

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

5 Comments

Haji Over a year ago

where will be the generated xml file location? Is there any default path exists?

Frank Heikens Over a year ago

@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.

Yavuz Selim Over a year ago

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?

wolf97084 Over a year ago

Can you also do query_to_xml on a CTE?

Atul Soni Over a year ago

To use xml feature in postgres, Postgres server must be first compiled with libxml library.

szeryf · Accepted Answer · 2012-04-04 18:02:28Z

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.

There is an interesting blog post about this matter: http://ryandaigle.com/articles/2007/4/13/what-s-new-in-edge-rails-a-more-flexible-to_xml

edited Apr 4, 2012 at 18:02

szeryf

3,4174 gold badges31 silver badges28 bronze badges

answered Aug 24, 2010 at 18:52

jigfox

18.2k3 gold badges62 silver badges74 bronze badges

1 Comment

Tian Over a year ago

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?

Collectives™ on Stack Overflow

Postgres Data to XML

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related