PostgreSQL xpath缺少父数据

vfwfrxfs 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(110)

我试图从一个XML结构中选择数据，其中2个字段在父项中，其余字段在多个子项中。

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

字符串
我的问题是父值只出现在第一个输出记录上，有没有一种方法可以让值在所有行上重复。
| 销售订单号|lineNumber| SerialNumber|数量|地位|重量|更新值|
| --|--|--|--|--|--|--|
| 123123123 | 10 | 47 | 1 |正常| 0 |没有|
| * 空 | 空 *| 48 | 1 |正常| 0 |没有|
| * 空 | 空 *| 49 | 1 |正常| 0 |没有|
| * 空 | 空 *| 50 | 1 |正常| 0 |没有|
| * 空 | 空 *| 51 | 1 |正常| 0 |没有|

postgresql

来源：https://stackoverflow.com/questions/77459575/postgresql-xpath-missing-parent-data

1条答案

按热度按时间

dxpyg8gm1#

请尝试以下基于XMLTABLE()的解决方案。
dbfiddle

Postres SQL

WITH rs AS
(
  SELECT XMLPARSE(DOCUMENT '<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 x
)
select t.*
from rs
cross join XMLTABLE(
      '/root/LINEINFO/SNUMBINFO'
       PASSING rs.x
       COLUMNS 
           salesordernumber varchar(64) PATH '../SONUMB',
           linenumber       int         PATH '../LINPOS',
           serialnumber     varchar(10) PATH 'SNUMB',
           quantity         int         PATH 'SQTY',
           status           varchar(10) PATH 'STATUS',
           weight           varchar(10) PATH 'WEIGHT',
           updatevalue      varchar(10) PATH 'UPDATE'
) as t;

字符串

输出

| 销售订单号|lineNumber| SerialNumber|数量|地位|重量|更新值|
| --|--|--|--|--|--|--|
| 123123123 | 10 | 47 | 1 |正常| 0 |没有|
| 123123123 | 10 | 48 | 1 |正常| 0 |没有|
| 123123123 | 10 | 49 | 1 |正常| 0 |没有|
| 123123123 | 10 | 50 | 1 |正常| 0 |没有|
| 123123123 | 10 | 51 | 1 |正常| 0 |没有|

赞(0）回复(0）举报 2023-11-18

我来回答

PostgreSQL xpath缺少父数据

1条答案

相关问题

热门标签

最新问答