PostgreSQL无法识别XML中的NULL值，解析int字段时报错怎么办？

在解析带有xsi:nil="true"的XML时，PostgreSQL的xmltable会将对应元素识别为空字符串而非NULL，当目标字段类型为int时，会触发invalid syntax for type integer: ""的错误。

你执行的原SQL语句：

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
)

以下两种方法可解决该问题，返回int类型的NULL值：

方法一：检测xsi:nil属性返回NULL #

通过xpath函数直接检查元素是否带有xsi:nil="true"属性，按需返回NULL或转换后的int值：

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 as (
        case 
            when xpath('boolean(int_value/@xsi:nil)', current_row) = array[true] 
            then null 
            else (xpath('int_value/text()', current_row))[1]::int 
        end
    )
)

• xpath('boolean(int_value/@xsi:nil)', current_row)：判断当前行的int_value元素是否标记为nil，返回布尔值数组。
• 利用case分支，当检测到nil标记时返回NULL，否则提取文本并转为int类型。

方法二：利用nullif转换空字符串为NULL #

针对空字符串无法转int的问题，先用nullif将空字符串转为NULL，再进行类型转换：

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 as (nullif(xpath('string(int_value)', current_row)[1], '')::int)
)

• xpath('string(int_value)', current_row)[1]：提取元素的字符串值，带nil标记的元素会返回空字符串。
• nullif(..., '')：将空字符串转换为NULL，再转为int类型，避免转换报错。

内容的提问来源于stack exchange，提问作者Valerian Kanzharadze