PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL does not recognise NULL-value in the XML

I am trying to parse XML using PostgreSQL.

XML contains a null-value. The expected type of column is "int".

But Postgres treats the value as an empty (not null) string.

with data as ( 
select 
'<root>
    <test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <row>
            <int_value xsi:nil="true"/>
        </row>    
    </test>
</root>'::xml val)
select int_value
from data x,
xmltable(
    '/root/test/row'
    passing val
    columns int_value int
)

I am expected to get query result with one field "int_value" of type "int" with null-value.

Actual result is error message:

ERROR: invalid syntax for type integer: "".
Tags:

2

Answers


  1. 0

    Since an empty string is not valid syntax for integer, do it like this.

    ...
select nullif(int_value, '')::integer as int_value
from data x,
xmltable(
    '/root/test/row'
    passing val
    columns int_value text
)
    Login or Signup to reply.
  2. 0

    First search for "postgresql convert empty string to null", you will land on the function NULLIF e.g. here

    This query transforms empty string to null and non-empty string cast to int

    select nullif(int_value,'')::int int_value

    Than change the column type of int_value (in XMLTABLE not in the result query) to text and rewrite the select as follows:

    with data as ( 
select 
'<root>
    <test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <row>
            <int_value xsi:nil="true"/>
        </row>    
    </test>
</root>'::xml val)
select nullif(int_value,'')::int int_value -- here cast test to int
from data x,
xmltable(
    '/root/test/row'
    passing val
    columns int_value text  -- here text
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search