xml - XPath 查询分层数据，保留祖先-后代关系

Question

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

我有一个具有多层层次结构的文档(在 PostgreSQL XML 值中)。对于这个问题，可以创建一个示例:

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;

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

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

不过，我想不通的是如何连接多个级别，以便查询返回与其适用的鹦鹉相关的每个描述符。

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

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

如何做到这一点？应该用什么来代替“???”？

单个复杂的 XPath 查询——但如何同时引用多个级别？几个 XPath 查询——但是如何为结果关系保留祖先-后代信息？还有别的吗？

Answer 1

试试这个:

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;

准确生成请求的输出。

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

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

我写了一个comprehensive answer to a related question最近。您可能会感兴趣。