Use XPath in PL/PGSQL for Batch Processing

Posted by in Software, Tutorial

A while ago I had to process XML loaded in a PostgreSQL database. The task was to extract references from a publication structure. Fortunately, PostgreSQL allows you to use XPath in plpgsql, but there are some things you need to be aware.

First, you need to note all namespaces:

Then, you can use the xpath() function:

…but that’s no fun, particularly when you are almost guaranteed to get multiple hits. It’s better to use:

A full implementation looks like this:

It gets all XML documents stored in a table with the column name article. For each document, get the references and increment a value. At the end we’ll get messages on how many items we had in the table and how many references.

For more information, go to the documentation.

A little experiment: If you find this post and ad below useful, please check the ad out 🙂