使用Postgres SQL中的xmltable解析XML

huangapple go评论118阅读模式
英文:

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;

Online example

huangapple
  • 本文由 发表于 2023年3月3日 19:21:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/75626438.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定