title: PostgreSQL Xpath to select element and it's child attribute as two columns
body: <p>I have a table <strong>history (id int, content xml)</strong> in postgreSQL. XML content for one of the id as as following</p>
  
  <pre><code>&lt;history-data&gt;
        &lt;history recorded-date="20110601"&gt;
          &lt;assignees&gt;
            &lt;assignee&gt;
               &lt;last-name&gt;CIENA LUXEMBOURG&lt;/last-name&gt;
            &lt;/assignee&gt;
          &lt;/assignees&gt;
          &lt;assignors&gt;
            &lt;assignor execution-date="20110517"&gt;
                &lt;last-name&gt;NORTEL NETWORKS LIMITED&lt;/last-name&gt;
            &lt;/assignor&gt;
          &lt;/assignors&gt;
        &lt;/history&gt;
        &lt;history recorded-date="20110601"&gt;
          &lt;assignees&gt;
            &lt;assignee&gt;
                &lt;last-name&gt;CIENA CORPORATION&lt;/last-name&gt;
            &lt;/assignee&gt;
          &lt;/assignees&gt;
          &lt;assignors&gt;
            &lt;assignor execution-date="20110527"&gt;
                &lt;last-name&gt;CIENA LUXEMBOURG&lt;/last-name&gt;
            &lt;/assignor&gt;
          &lt;/assignors&gt;
        &lt;/history&gt;
        &lt;history recorded-date="20090430"&gt;
          &lt;assignees&gt;
            &lt;assignee&gt;
               &lt;last-name&gt;NORTEL NETWORKS&lt;/last-name&gt; 
            &lt;/assignee&gt;
          &lt;/assignees&gt;
          &lt;assignors&gt;
            &lt;assignor execution-date="20090424"&gt;
                &lt;last-name&gt;MAK, GARY&lt;/last-name&gt;
            &lt;/assignor&gt;
            &lt;assignor execution-date="20090424"&gt;
                &lt;last-name&gt;VELEZ, EDGAR&lt;/last-name&gt;
            &lt;/assignor&gt;
          &lt;/assignors&gt;
        &lt;/history&gt;
      &lt;/history-data&gt;
  </code></pre>
  
  <p>Here, i want to get last-name &amp; it's respective execution-date. For the above example, i want the following output</p>
  
  <pre><code>last-name                   execution-date
  ================            ==============
  CIENA LUXEMBOURG              20110517
  CIENA CORPORATION             20110527
  NORTEL NETWORKS               20090424
  </code></pre>
  
  <p>I am able to generate all possible combinations using the following SQL query but not able to get output like above</p>
  
  <pre><code>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
  </code></pre>
  
  <p>any suggestions on how can this be done ?</p>