3

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.

1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.