Migrating to PostgreSQL 'xml' datatype

Question

G'day,

I have databases (psql 8.3) that currently consist of "xml" blobs as text type within the database. Now, I've gone through each of these blobs, created a schema which the data should adhere to and made sure the current blobs in the DB validate against that schema. Now, my question is:

Should I decide to change the type of these blobs from text to xml, would I encounter any major problems?

What advantage would that give me over my current set-up?

Thanks!

user330315 · Accepted Answer · 2011-01-20 07:22:00Z

1

It depends on how you use that XML data.

If you always retrieve them from the database as a single value and do all XML processing (e.g. XSLT or extracting information) in your application I would stick with the text data type.

If you regularily query the XML using PostgreSQL xml functions and need to index those queries, then using a xmltype column is probably better.

answered Jan 20, 2011 at 7:22

user330315

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

2 Comments

Gaurav Dadhania Over a year ago

Do you know of any automatic indexing that PostgreSQL 8.3 offers? Currently most of our use is just retrieving it from the DB and working on it in the application. But sometimes we need to execute complex Xpath queries. So auto-indexing with Xpath to SQL querying would be enough to convince me to change. Thanks.

user330315 Over a year ago

There is no such feature as automatic indexing (actually I don't know any DBMS that will automatically create indexes based on your queries - would be a nifty feature though). Just create an index with the XPATH you use most often.

Peter Eisentraut · Accepted Answer · 2011-01-20 08:26:03Z

0

This is explained here: http://www.postgresql.org/docs/8.3/static/datatype-xml.html

Its advantage over storing XML data in a text field is that it checks the input values for well-formedness, and there are support functions to perform type-safe operations on it.

So I'd say you should use it.

answered Jan 20, 2011 at 8:26

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Collectives™ on Stack Overflow

Migrating to PostgreSQL 'xml' datatype

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related