Postgresql query XMLTable with default namespace

postgresql-12xml

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.

Best Answer

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

Related Question