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 ?

Dzmitry Savinkou · Accepted Answer · 2016-01-13 08:19:43Z

16

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;

edited Jan 13, 2016 at 8:19

answered Jan 12, 2016 at 15:56

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

2 Comments

Gaini Rajeshwar Over a year ago

Thank you. This works fine but it is not giving correct results when i have multiple assignees & one assignor in single history. In this case i want to get both assignees with same execution-date. Any idea on how can this be done ?

Dzmitry Savinkou Over a year ago

just use unnest() when you do have multiple entries of assignee @GainiRajeshwar

jcaron · Accepted Answer · 2016-01-14 09:20:28Z

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;

edited Jan 14, 2016 at 9:20

answered Jan 12, 2016 at 15:54

jcaron

17.8k6 gold badges36 silver badges47 bronze badges

4 Comments

Gaini Rajeshwar Over a year ago

Thank you. Yes, this works fine when there is single assignee. I want this to work even when there are several assignees in a single history element. In this case i want all assignees with first execution-date (in case if there are multiple assignors & execution-dates in single history)

jcaron Over a year ago

First date listed, or first date in chronological order (if there's a difference)?

Gaini Rajeshwar Over a year ago

@jacron first date listed

Gaini Rajeshwar Over a year ago

@jacron Thank you for that. But group by won't work on xml element here. You have to cast it into text or any other datatype. updated the answer accordingly.

Collectives™ on Stack Overflow

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

2 Answers 2

EDITION

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDITION

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related