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

Question

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

9

我在PostgreSQL中有一个表history（id int，content xml）。其中一个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

任何关于如何完成这件事的建议吗？

- Gaini Rajeshwar

2个回答

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;

请注意，如果在单个history元素中有多个assignee，可能会得到奇怪的结果。另外，不确定您是否想要所有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;

- jcaron

谢谢。是的，当只有一个受让人时，这个功能可以正常工作。但我希望即使在单个历史元素中有多个受让人时，它也能正常工作。在这种情况下，我希望所有受让人都具有第一个执行日期（如果在单个历史记录中有多个受让人和执行日期）。 - Gaini Rajeshwar

首先列出的日期，还是按时间顺序排列的第一个日期？ - jcaron

@jacron 首次列出的日期 - Gaini Rajeshwar

@jacron 谢谢你的回复。但是在这里，无法对 XML 元素使用 group by。您必须将其转换为文本或任何其他数据类型。已相应地更新了答案。 - Gaini Rajeshwar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Dmitry S · Accepted Answer

你需要遍历所有的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;