Postgresql query XMLTable with default namespace

Question

I use PostgreSQL 12 to query XML data.

My data is as below

CREATE TABLE xmldata AS SELECT
xml $$
<ROWS xmlns="http://x.y.z">
 <ROW id="1">
 <COUNTRY_ID>AU</COUNTRY_ID>
 <COUNTRY_NAME>Australia</COUNTRY_NAME>
 </ROW>
 <ROW id="5">
 <COUNTRY_ID>JP</COUNTRY_ID>
 <COUNTRY_NAME>Japan</COUNTRY_NAME>
 <PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
 <SIZE unit="sq_mi">145935</SIZE>
 </ROW>
 <ROW id="6">
 <COUNTRY_ID>SG</COUNTRY_ID>
 <COUNTRY_NAME>Singapore</COUNTRY_NAME>
 <SIZE unit="sq_km">697</SIZE>
 </ROW>
</ROWS>
$$ AS data;

When query, my SQL is as below:

SELECT xmltable.*
 FROM xmldata,
 XMLTABLE(namespaces(default 'http://x.y.z'), '//ROWS/ROW'
 PASSING data
 COLUMNS id int PATH '@id',
 ordinality FOR ORDINALITY,
 "COUNTRY_NAME" text,
 country_id text PATH 'COUNTRY_ID',
 size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
 size_other text PATH
 'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
 premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;

It happened this error:

DEFAULT namespace is not supported

Please instruct how to fix it ? Many thanks.

Answer 1

As documented in the manual "A default namespace specification is not currently supported"

You need to give the namespace an alias and use that in the XPath expressions:

SELECT xmltable.*
FROM xmldata,
 XMLTABLE(
 xmlnamespaces('http://x.y.z' as x),
 '//x:ROWS/x:ROW'
 PASSING data
 COLUMNS 
 id int PATH '@id',
 ordinality FOR ORDINALITY,
 "x:COUNTRY_NAME" text,
 country_id text PATH 'x:COUNTRY_ID',
 size_sq_km float PATH 'x:SIZE[@unit = "sq_km"]',
 size_other text PATH 
 'concat(x:SIZE[@unit!="sq_km"], " ", x:SIZE[@unit!="sq_km"]/@unit)',
 premier_name text PATH 'x:PREMIER_NAME' DEFAULT 'not specified'
 ) ;

Online example