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…read more

Add XML to PostgreSQL from Python

Posted by in Software

One of the projects I worked on was to import a large number of XML files in a PostgreSQL database (as XML files). I chose python to do it. Here are the steps: Database The data source is the SCOPUS database which has a silly number of entries (approx 20,000,000), most containing 2 XML files (article and citations). The publications with no citations don’t have the citations.xml file. Therefore, initial database has a single table named scpous_data with the following fields: id – string of 20 characters. This is the…read more

Loading XSDs into Oracle through Python

Posted by in Software

Following the previous incursions in Oracle and their own documentation, we decided it was useful to attach schemas to the inserted data, just because only XML with schemas attached can be indexed. When you have some million entries, indexing seems like a good idea (once you decide what to index). In Oracle, you have DBMS_XMLSCHEMA.registerSchema() to do just that. It has a bunch of parameters which are thoroughly described in their documentation. Suffices to say that you can choose to generate tables, objects or java beans off the loaded schemas….read more

Memory dump: Work with XMLField in Oracle (part 2)

Posted by in Software

TL;DR: Here I present a SQL example from creating the table to performing a SELECT. Table of Contents Part 1 – Prerequisites Part 2 – Create a table and perform a SELECT Part 3 – User Python to insert data Part 4 – references Table and XMLTYPE Once we’ve seen that we have XMLTYPE available, we can proceed to create a simple table. The table structure My problem required for a table with the following structure: An identifier (key) – 20 character long string An article field (nullable) – XML…read more

Install lxml on windows (on a virtualenv)

Posted by in Software

Lxml is a nice python library for XML processing. ETree is really quick, which makes things interesting if you have a large amount of XML files (or a bigger one) to process. Installation on linux/mac is painless (OK, you need homebrew on mac to make int painless, but you get my point…). The other day I had to do it on windows. This is not really painless. I managed to do it, so here are the steps. Prerequisites Python 2.7 VC++ 2880, the Express edition (freely downloadable from Microsoft), because…read more