PostgreSQL XPath查询父级字段仅首行显示，如何实现全行列重复？

我尝试从XML结构中查询数据，其中2个字段位于父节点，其余字段分布在多个子节点中。当前使用的SQL查询如下，但查询结果里父级字段（salesordernumber、linenumber）仅在第一条记录显示，后续记录都是null，请问怎么让父级字段在所有结果行重复显示？

SELECT  
                CAST(unnest(xpath('//LINEINFO/SONUMB/text()', node::xml))AS TEXT)   AS salesordernumber,
                CAST(unnest(xpath('//LINEINFO/LINPOS/text()', node::xml))AS TEXT)   AS linenumber,
                CAST(unnest(xpath('//SNUMBINFO/SNUMB/text()', node::xml)) AS TEXT)  AS serialnumber,
                CAST(unnest(xpath('//SNUMBINFO/SQTY/text()', node::xml)) AS TEXT)   AS quantity,
                CAST(unnest(xpath('//SNUMBINFO/STATUS/text()', node::xml)) AS TEXT) AS status,
                CAST(unnest(xpath('//SNUMBINFO/WEIGHT/text()', node::xml)) AS TEXT) AS weight,
                CAST(unnest(xpath('//SNUMBINFO/UPDATE/text()', node::xml)) AS TEXT) AS updatevalue
        FROM    (
                    SELECT  '<root>
    <LINEINFO>
        <SONUMB>123123123</SONUMB>
        <LINPOS>10</LINPOS>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>47</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>48</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>49</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>50</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>51</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
    </LINEINFO>
</root>' as node
                ) tbl_productionorder

当前查询结果 #

解决方案 #

问题出在直接对所有xpath结果做unnest，父节点的xpath仅返回1个值，unnest后只有第一行有数据，后续行自然为null。正确做法是先提取父节点的固定值，再关联所有子节点数据，确保父值每行重复。

修改后的SQL如下：

SELECT  
    line_info.salesordernumber,
    line_info.linenumber,
    CAST(xpath('./SNUMB/text()', snumb_node) AS TEXT) AS serialnumber,
    CAST(xpath('./SQTY/text()', snumb_node) AS TEXT) AS quantity,
    CAST(xpath('./STATUS/text()', snumb_node) AS TEXT) AS status,
    CAST(xpath('./WEIGHT/text()', snumb_node) AS TEXT) AS weight,
    CAST(xpath('./UPDATE/text()', snumb_node) AS TEXT) AS updatevalue
FROM (
    SELECT  
        CAST(xpath('//LINEINFO/SONUMB/text()', node::xml) AS TEXT) AS salesordernumber,
        CAST(xpath('//LINEINFO/LINPOS/text()', node::xml) AS TEXT) AS linenumber,
        unnest(xpath('//LINEINFO/SNUMBINFO', node::xml)) AS snumb_node
    FROM (
        SELECT  '<root>
    <LINEINFO>
        <SONUMB>123123123</SONUMB>
        <LINPOS>10</LINPOS>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>47</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>48</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>49</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>50</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
        <SNUMBINFO>
            <STATUS>Normal</STATUS>
            <SQTY>1</SQTY>
            <SNUMB>51</SNUMB>
            <UPDATE>no</UPDATE>
            <WEIGHT>0</WEIGHT>
        </SNUMBINFO>
    </LINEINFO>
</root>' as node
    ) tbl_productionorder
) line_info;

核心逻辑说明 #

1. 子查询中先提取父节点的salesordernumber和linenumber，这两个是单值，无需unnest
2. 用unnest(xpath('//LINEINFO/SNUMBINFO', node::xml))把所有SNUMBINFO子节点拆分成单独行
3. 外层查询针对每个子节点snumb_node，用相对路径./XX/text()提取子字段，同时直接引用父节点的固定值，实现每行都携带父节点数据

内容的提问来源于stack exchange，提问作者Gavin