It's not possible to use the XMLTABLE() function with PostgreSQL

[olivierauverlot]

Hi.

I need to use the XML functions of PostgreSQL for parsing XML data. My problem is when I want use the XMLTABLE() function (https://www.postgresql.org/docs/15/functions-xml.html). The parsing of the SQL code by SQLPage fails.

You can reproduce the bug with the following example :

SET data = '<ROWS>
  <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>';

DROP TABLE IF EXISTS xmldata;
CREATE TEMPORARY TABLE xmldata AS SELECT $data::xml as data;

SELECT xmltable.*
    FROM xmldata,
    XMLTABLE('//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');

I got a sqlPage error message :

Parsing failed: SQLPage couldn't understand the SQL file. Please check for syntax errors:

    FROM xmldata,
    XMLTABLE('//ROWS/ROW'
                         ⬆️
        PASSING data

sql parser error: Expected: ), found: PASSING at Line: 25, Column: 9

"xmltable.sql" contains a syntax error preventing SQLPage from parsing and preparing its SQL statements.

The expected behavior is that the temporary table should contain :

 id | ordinality | COUNTRY_NAME | country_id | size_sq_km |  size_other  | premier_name
----+------------+--------------+------------+------------+--------------+---------------
  1 |          1 | Australia    | AU         |            |              | not specified
  5 |          2 | Japan        | JP         |            | 145935 sq_mi | Shinzo Abe
  6 |          3 | Singapore    | SG         |        697 |              | not specified

• OS: Linux Debian 12
• PostgreSQL 15
• SQLPage 0.34 (latest)

Thank you for your help

Best regards Olivier

[lovasoa]

Thank you for the detailed report ! I opened an issue upstream: https://github.com/apache/datafusion-sqlparser-rs/issues/1816

[lovasoa]

In the meantime, the easiest way for you to work around the problem is to define your own user-defined function in postgres that wraps xmltable, but does not require any special syntax. Then you can call your own function instead of xmltable from SQLPage.

CREATE OR REPLACE FUNCTION parse_xml_rows(xml_data xml)
RETURNS TABLE (
    id int,
    ordinality int,
    country_name text,
    country_id text,
    size_sq_km float,
    size_other text,
    premier_name text
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        x.id,
        x.ordinality,
        x."COUNTRY_NAME",
        x.country_id,
        x.size_sq_km,
        x.size_other,
        x.premier_name
    FROM XMLTABLE(
        '//ROWS/ROW'
        PASSING xml_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'
    ) AS x;
END;
$$ LANGUAGE plpgsql;

[olivierauverlot]

Yes, that's a good idea. It's a temporary solution to the problem. In the short term, a stored procedure solves the SQL parser problem.

[lovasoa]

Good news. My implementation has been merged upstream, xmltable() is coming to the next version of sqlpage :)

[lovasoa]

fixed by https://github.com/sqlpage/SQLPage/commit/ac24c052692cf1952e78f40e901edcfcb746500a