2

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!

    Improve this question

    2 Answers 2

    Reset to default
    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.

    Improve this answer
    2
    • 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. Commented Jan 20, 2011 at 23:12
    • 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. Commented Jan 20, 2011 at 23:16
    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.

    Improve this answer

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

    Start asking to get answers

    Find the answer to your question by asking.

    Ask question

    Explore related questions

    See similar questions with these tags.