PostgreSQL Xpath to select element and it's child attribute as two columns

Question

I have a table history (id int, content xml) in postgreSQL. XML content for one of the id as as following

<history-data>
      <history recorded-date="20110601">
        <assignees>
          <assignee>
             <last-name>CIENA LUXEMBOURG</last-name>
          </assignee>
        </assignees>
        <assignors>
          <assignor execution-date="20110517">
              <last-name>NORTEL NETWORKS LIMITED</last-name>
          </assignor>
        </assignors>
      </history>
      <history recorded-date="20110601">
        <assignees>
          <assignee>
              <last-name>CIENA CORPORATION</last-name>
          </assignee>
        </assignees>
        <assignors>
          <assignor execution-date="20110527">
              <last-name>CIENA LUXEMBOURG</last-name>
          </assignor>
        </assignors>
      </history>
      <history recorded-date="20090430">
        <assignees>
          <assignee>
             <last-name>NORTEL NETWORKS</last-name> 
          </assignee>
        </assignees>
        <assignors>
          <assignor execution-date="20090424">
              <last-name>MAK, GARY</last-name>
          </assignor>
          <assignor execution-date="20090424">
              <last-name>VELEZ, EDGAR</last-name>
          </assignor>
        </assignors>
      </history>
    </history-data>

Here, i want to get last-name & it's respective execution-date. For the above example, i want the following output

last-name                   execution-date
================            ==============
CIENA LUXEMBOURG              20110517
CIENA CORPORATION             20110527
NORTEL NETWORKS               20090424

I am able to generate all possible combinations using the following SQL query but not able to get output like above

SELECT id, unnest(CAST(xpath('/history-data/history/assignees/assignee/last-name/text()',content) AS text)::text[]) AS last-name,
unnest(CAST(xpath('/history-data/history/assignors/assignor/@execution-date',content) AS text)::text[]) AS execution-date
FROM history
WHERE id = 10

any suggestions on how can this be done ?

Answer 1

You need to iterate over all history nodes and get appropriate elements with xpath() function. By default, result of xpath extraction returns array of xml, that's why we need to get actual value with array index (...)[1]; sample query may be the following:

SELECT
  (xpath('//assignee/last-name/text()',xml_element))[1] AS "last-name",
  (xpath('//assignor/@execution-date',xml_element))[1] AS "execution-date"
FROM (
  SELECT unnest(xpath('//history',content)) AS xml_element FROM history
  WHERE id = 10
) t;

Result is:

     last-name     | execution-date 
-------------------+----------------
 CIENA LUXEMBOURG  | 20110517
 CIENA CORPORATION | 20110527
 NORTEL NETWORKS   | 20090424
(3 rows)

EDITION

When assignees has multiple assagnee nodes query should use unnest() to get all array elements:

SELECT
  unnest(xpath('//assignee/last-name/text()',xml_element)) AS "last-name",
  unnest(xpath('//assignor/@execution-date',xml_element)) AS "execution-date"
FROM (
  SELECT unnest(xpath('//history',content)) AS xml_element FROM history
  WHERE id = 10
) t;

Answer 2

What your request does is find all the assignees, and, independently, find all the execution dates, and returns the cartesian product, which is probably not what you actually want.

What you want is:

• find all history elements
• then for each history element, find the text/attribute you're interested in.

This means using a subquery:

SELECT
    unnest(xpath('./assignees/assignee/last-name/text()',item))::text,
    unnest(xpath('./assignors/assignor/@execution-date',item))::text
FROM (
    SELECT
        unnest(xpath('/history-data/history',content)) AS item
    FROM history
    WHERE id = 10
    ) s
GROUP BY 1,2;

Note that you will probably get weird results if you have several assignees in a single history element. Also, not sure if you want all execution-dates, or just the first, or last, or...

EDIT

To get all assignees, but only the first execution-date listed:

SELECT
    unnest(xpath('./assignees/assignee/last-name/text()',item))::text,
    (xpath('./assignors/assignor/@execution-date',item))[1]::text
FROM (
    SELECT
        unnest(xpath('/history-data/history',content)) AS item
    FROM history
    WHERE id = 10
    ) s
GROUP BY 1,2;