英文:
Parsing xml using xmltable in Postgres Sql
问题
在通过PostgreSQL SQL解析XML结构时,我正在使用XMLTABLE,我遇到的问题是如果子节点不存在,整行都会被丢弃。例如,考虑以下XML:
<cars>
<car>
<manufacturer>Ford</manufacturer>
<name>Fiesta</name>
<id>20060</id>
<currency>GBP</currency>
<price>17000</price>
<specifications>
<feature>
<safety>Airbags</safety>
<engine>Petrol</engine>
</feature>
<feature>
<safety>ABS</safety>
<engine>Diesel</engine>
</feature>
</specifications>
</car>
<car>
<manufacturer>BMW</manufacturer>
<name>3-Series</name>
<id>3456</id>
<currency>EUR</currency>
<price>32000</price>
</car>
</cars>
为了解析这个XML,使用了以下查询:
with data as
(
select *, myxml::xml as query_column
from mytable
)
select xml.manufacturer,xml.name,xml.currency,xml.price,xml.safety,xml.engine
FROM data,
XMLTABLE ('/cars/car/specifications/feature' PASSING query_column
COLUMNS manufacturer text PATH '../../manufacturer',
name text PATH '../../name',
currency text PATH '../../currency',
price text PATH '../../price',
safety text path 'safety',
engine text path 'engine') xml
它只返回了前两行,将BMW的第三行丢弃了。期望结果应该包括3行,在第三行中,safety和engine应该为NULL。
实际输出:
manufacturer | name | currency | price | safety | engine |
---|---|---|---|---|---|
Ford | Fiesta | GBP | 17000 | Airbags | Petrol |
Ford | Fiesta | GBP | 17000 | ABS | Diesel |
期望输出:
manufacturer | name | currency | price | safety | engine |
---|---|---|---|---|---|
Ford | Fiesta | GBP | 17000 | Airbags | Petrol |
Ford | Fiesta | GBP | 17000 | ABS | Diesel |
BMW | 3-Series | EUR | 32000 | null | null |
英文:
While parsing a xml structure through postgres sql, I am using xmltable, the issue I am facing is if a child node is not present whole row is getting dropped.
For instance, consider below xml:
<cars>
<car>
<manufacturer>Ford</manufacturer>
<name>Fiesta</name>
<id>20060</id>
<currency>GBP</currency>
<price>17000</price>
<specifications>
<feature>
<safety>Airbags</safety>
<engine>Petrol</engine>
</feature>
<feature>
<safety>ABS</safety>
<engine>Diesel</engine>
</feature>
</specifications>
</car>
<car>
<manufacturer>BMW</manufacturer>
<name>3-Series</name>
<id>3456</id>
<currency>EUR</currency>
<price>32000</price>
</car>
</cars>
To parse this, following query was used:
with data as
(
select *, myxml::xml as query_column
from mytable
)
select xml.manufacturer,xml.name,xml.currency,xml.price,xml.safety,xml.engine
FROM data,
XMLTABLE ('/cars/car/specifications/feature' PASSING query_column
COLUMNS manufacturer text PATH '../../manufacturer',
name text PATH '../../name',
currency text PATH '../../currency',
price text PATH '../../price',
safety text path 'safety',
engine text path 'engine') xml
It is giving only first 2 rows, dropping the third one with BMW.
Expectation is, it should result with 3 rows, in third row, safety and engine should be NULL.
> Getting output:
manufacturer | name | currency | price | safety | engine |
---|---|---|---|---|---|
Ford | Fiesta | GBP | 17000 | Airbags | Petrol |
Ford | Fiesta | GBP | 17000 | ABS | Diesel |
> Expected output:
manufacturer | name | currency | price | safety | engine |
---|---|---|---|---|---|
Ford | Fiesta | GBP | 17000 | Airbags | Petrol |
Ford | Fiesta | GBP | 17000 | ABS | Diesel |
BMW | 3-Series | EUR | 32000 | null | null |
答案1
得分: 1
你需要从/cars/car
路径开始,对于每辆汽车,使用另一个(外连接)来获取规格信息。你需要使用外连接而不是(隐式)交叉连接,以便包括那些没有规格信息的行。由于没有真正的连接列,我们使用on true
作为连接条件:
select c.id,
c.manufacturer,
c.name,
c.currency,
c.price,
s.*
FROM data
left join xmltable ('/cars/car' PASSING myxml
COLUMNS
manufacturer text PATH 'manufacturer',
name text PATH 'name',
currency text PATH 'currency',
price text PATH 'price',
id int path 'id',
specification xml path 'specifications'
) c on true
left join xmltable ('/specifications/feature' passing c.specification
columns
safety text path 'safety',
engine text path 'engine'
) s on true;
英文:
You need to start with the /cars/car
path and for each car, use another (outer join) to get the specifications. You need to use an outer join not an (implicit) cross join to also include rows that don't have that. As there is no real join column, we use on true
as the join condition:
select c.id,
c.manufacturer,
c.name,
c.currency,
c.price,
s.*
FROM data
left join xmltable ('/cars/car' PASSING myxml
COLUMNS
manufacturer text PATH 'manufacturer',
name text PATH 'name',
currency text PATH 'currency',
price text PATH 'price',
id int path 'id',
specification xml path 'specifications'
) c on true
left join xmltable ('/specifications/feature' passing c.specification
columns
safety text path 'safety',
engine text path 'engine'
) s on true;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论