选择元素的PostgreSQL Xpath及其作为两列的子属性

Question

我在中有一个表 (id int，content )。其中一个id的XML内容如下所示

<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>

这里，我想知道姓&这是各自的执行日期。对于上面的示例，我希望得到以下输出

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

我能够使用以下SQL查询生成所有可能的组合，但无法像上面那样获得输出

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

对于如何做到这一点，有什么建议吗？

Answer 1

您需要对所有history节点进行迭代，并使用xpath()函数获得适当的元素。默认情况下，xpath提取的结果返回xml的数组，这就是我们需要使用数组索引(...)[1]获取实际值的原因；示例查询可能如下所示：

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;

结果是：

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

版本

当assignees有多个assagnee节点时，查询应该使用unnest()获取所有数组元素：

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

您的请求是查找所有的受让人，独立地查找所有的执行日期，并返回笛卡尔积，这可能不是您真正想要的。

你想要的是：

• 查找所有history元素
• 然后，对于每个history元素，查找您感兴趣的文本/属性。

这意味着使用子查询：

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;

请注意，如果单个assignee元素中有多个history，则可能会得到奇怪的结果。另外，您也不确定是否需要所有的execution-date，或者仅仅是第一个，还是最后一个，或者.

编辑

要获得所有的assignee，但只列出了第一个execution-date：

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;