Parsing xml using xmltable in Postgres Sql

Question

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

user330315 · Accepted Answer · 2023-03-03 14:51:50Z

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

Collectives™ on Stack Overflow

Parsing xml using xmltable in Postgres Sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related