CODEWITHSUNDEEP
sqlxmlpostgresql
user16120
user16120

Reputation: 972

Postgres XML datatype

What are the benefits of using the "xml" datatype versus storing the xml content inside a "text" datatype?

Am I able to query by some specific xml attribute or element?

What about indexing and query performance?

Besides the postgresql manual what other online sources can you point me to?

Upvotes: 16

Views: 13790

Answers (2)

Peter Eisentraut
Peter Eisentraut

Reputation: 36749

Generally speaking, the benefits are the same ones as for any other data type and why you have data types other than text at all:

  • Data integrity
    You can only store valid (well, well-formed) XML values in columns of type xml.
  • Type safety
    You can only perform operations on XML values that make sense for XML.

One example is the xpath() function (XML Path Language), which only operates on values of type xml, not text.

Indexing and query performance characteristics are not better or worse than for say the text type at the moment.

Upvotes: 15

Jeff Mc
Jeff Mc

Reputation: 3793

Right now the biggest thing you get from XML fields over raw text is XPath. So if you had something similar to

CREATE TABLE pages (id int, html xml);

you could get the title of page 4 by

SELECT xpath('/html/head/title/text()', html) FROM pages WHERE id = 4;

Right now XML support is fairly limited, but got a lot better in 8.3, current docs are at link text

Upvotes: 19

Related Questions