PostgreSQL中使用XMLTABLE解析XML时子节点缺失致行丢失问题

问题根源 #

原查询的XPath直接定位到/cars/car/specifications/feature，只有存在specifications/feature子节点的汽车才会生成结果行，缺失该子节点的BMW数据被直接过滤，因此仅返回Ford的两行数据。

解决方案 #

先遍历所有car节点提取基础信息，再通过LEFT JOIN LATERAL左关联解析每个汽车下的feature节点——如果不存在该子节点，对应的safety和engine字段会返回NULL，以此确保所有汽车数据都能被保留。

修改后的查询语句 #

WITH data AS (
    SELECT *, myxml::xml AS query_column 
    FROM mytable
)
SELECT 
    car.manufacturer,
    car.name,
    car.currency,
    car.price,
    feature.safety,
    feature.engine
FROM data,
     -- 第一步：解析所有car节点，提取基础信息和当前car的XML节点
     XMLTABLE ('/cars/car' PASSING query_column
               COLUMNS 
                   manufacturer TEXT PATH 'manufacturer',
                   name TEXT PATH 'name',
                   currency TEXT PATH 'currency',
                   price TEXT PATH 'price',
                   car_xml XML PATH '.'
              ) car
-- 第二步：左关联解析每个car下的feature节点，无节点则返回NULL
LEFT JOIN LATERAL XMLTABLE ('/car/specifications/feature' PASSING car.car_xml
                            COLUMNS 
                                safety TEXT PATH 'safety',
                                engine TEXT PATH 'engine'
                           ) feature ON true;

结果说明 #

执行该查询后会返回三行数据：

• Ford Fiesta的两行feature数据（分别对应Airbags/Petrol、ABS/Diesel）
• BMW 3-Series的一行数据，其中safety和engine字段为NULL

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