How to join tables based on XPath results in PostgreSQL?

Question

I've got a table views with a column containing XML documents (stored as text). Here's a simplified version:

<?xml version="1.0"?>
<view>
  <image>
    <sequence>
      <param name="scan">129</param>
    </sequence>
  </image>
  <image>
    <sequence>
      <param name="scan">1</param>
    </sequence>
  </image>
  [...]
</view>

Another table contains the scans referenced in the XML. If refactoring into a new table is impractical at the moment, how do I connect the two tables in an SQL query?

l0b0 · Accepted Answer · 2013-01-16 09:26:53Z

3

If your PostgreSQL has been compiled with --with-libxml, the following query should work:

SELECT ...
  FROM views
  JOIN scans ON scans.id = ANY (
           xpath(
               '/view/image/sequence/param[@name = "scan"]/text()',
                views.xml::xml)::text[]::int[])
;

That is:

Cast views.xml from text to xml.
Get all the scan ID values as an array of xml using xpath().
Cast the xml array to an array of text and then an array of int (you can't cast xml[] to int[] directly, at least in PostgreSQL 9.2).
Check that at least one of the scan IDs from the XML matches.

edited Jan 16, 2013 at 9:26

answered Jan 15, 2013 at 9:21

l0b0

59.7k32 gold badges155 silver badges248 bronze badges

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

Collectives™ on Stack Overflow

How to join tables based on XPath results in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related