I've been giving this a look. I started by tweaking the docs once
again, and while verifying that the example works as expected, I
replayed what I have in sgml:
... begin SGML paste ... <para> For example, given the following XML document: <screen><![CDATA[
<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>Sinzo Abe</PREMIER_NAME> </ROW> <ROW
id="6"> <COUNTRY_ID>SG</COUNTRY_ID> <COUNTRY_NAME>Singapore</COUNTRY_NAME> <SIZE unit="km">791</SIZE> </ROW>
</ROWS>
]]></screen>
the following query produces the result shown below:
<screen><![CDATA[
SELECT xmltable.* FROM (SELECT data FROM xmldata) x, LATERAL xmltable('//ROWS/ROW'
PASSINGdata COLUMNS id int PATH '@id', ordinality FOR ORDINALITY,
country_name text PATH 'COUNTRY_NAME', country_id text
PATH'COUNTRY_ID', size float PATH 'SIZE[@unit = "km"]/text()',
unit text PATH 'SIZE/@unit', premier_name text PATH 'PREMIER_NAME' DEFAULT 'not
specified');
... end SGML paste ...
But the query doesn't actually return a table, but instead it fails with
this error: ERROR: invalid input syntax for type double precision: ""
This is because of the "size" column (if I remove SIZE from the COLUMNS
clause, the query returns correctly). Apparently, for the rows where
SIZE is not given, we try to inssert an empty string instead of a NULL
value, which is what I expected.
I'm using your v44 code, but trimmed both the XML document used in SGML
as well as modified the query slightly to show additional features. But
those changes should not cause the above error ...
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
