XPath查询到分层数据中，保留祖先与后裔的关系 [英] XPath query into hierarchical data, preserving ancestor–descendant relationship

问题描述

如何向PostgreSQL表示我想要从XPath查询的几个层次级别同时获取值？

How can I express to PostgreSQL that I want values simultaneously from several hierarchical levels in an XPath query?

我有一个文档（在具有多层次结构的PostgreSQL XML 值中）。对于此问题，可以使用以下示例创建一个示例：

I have a document (in a PostgreSQL XML value) with a multi-level hierarchy. For this question, an example can be created with:

SELECT XMLPARSE(DOCUMENT '
    <parrots>
        <parrot name="Fred">
            <descriptor>Beautiful plumage</descriptor>
            <descriptor>Resting</descriptor>
        </parrot>
        <parrot name="Ethel">
            <descriptor>Pining for the fjords</descriptor>
            <descriptor>Stunned</descriptor>
        </parrot>
    </parrots>
    ') AS document
INTO TEMPORARY TABLE parrot_xml;

我可以从该文档中获得不同级别的信息。

I can get different levels of information from that document.

=> SELECT
        (XPATH('./@name', parrot.node))[1] AS name
    FROM (             
        SELECT
            UNNEST(XPATH('./parrot', parrot_xml.document))
                AS node
        FROM parrot_xml
        ) AS parrot
    ;
 name  
-------
 Fred
 Ethel
(2 rows)

=> SELECT
        (XPATH('./text()', descriptor.node))[1] AS descriptor
    FROM (
        SELECT
            UNNEST(XPATH('./parrot/descriptor', parrot_xml.document))
                AS node
        FROM parrot_xml
        ) AS descriptor
    ;
      descriptor       
-----------------------
 Beautiful plumage
 Resting
 Pining for the fjords
 Stunned
(4 rows)

我不知道但是，如何连接多个级别是使查询返回与其所应用的鹦鹉相关的每个描述符的方法。

What I can't figure out, though, is how to get multiple levels joined, so that the query returns each descriptor related with the parrot to which it applies.

=> SELECT
        ??? AS name,
        ??? AS descriptor
    FROM
        ???
    ;

 name         descriptor       
------- -----------------------
 Fred    Beautiful plumage     
 Fred    Resting               
 Ethel   Pining for the fjords 
 Ethel   Stunned               
(4 rows)

这怎么可能完成了吗

单个复杂的XPath查询应该如何代替 ??? ？

A single complex XPath query – but how to refer to multiple levels at once? Several XPath queries – but then how is the ancestor–descendant information preserved for the resulting relation? Something else?

推荐答案

尝试一下：

SELECT (xpath('./@name', parrot.node))[1] AS name
     , unnest(xpath('./descriptor/text()', parrot.node)) AS descriptor
FROM  (             
   SELECT unnest(xpath('./parrot', parrot_xml.document)) AS node
   FROM   parrot_xml
   ) parrot;

精确地生成请求的输出。

Produces exactly the requested output.

首先，在子查询中，我检索了整个鹦鹉节点。每行一个节点。

First, in the subquery, I retrieve whole parrot-nodes. One node per row.

接下来，我使用xpath（）获得名称和描述符。两者都是数组。我采用 name 的第一个（也是唯一一个）元素，并用`unnest（）拆分 descriptor 数组，从而得出

Next, I get the name and the descriptors with xpath(). Both are arrays. I take the first (and only) element of name and split the descriptor array with `unnest(), thereby arriving at the desired result.

我写了对相关问题的综合答案最近。您可能会感兴趣。

I wrote a comprehensive answer to a related question recently. May be of interest to you.

这篇关于XPath查询到分层数据中，保留祖先与后裔的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！