PostgreSQL 9.6 xml2扩展：Unnest(xpath())无法正确识别NULL值

嘿，这个问题我在PostgreSQL 9.6处理XML的时候也踩过坑！用unnest(xpath())提取空节点时，默认不会返回SQL NULL而是空数组或者空字符串，给你几个实用的解决办法：

方法1：用NULLIF转换空数组为NULL #

这是最简洁的方案，针对你的XML结构，提取每个<row>节点的字段时，把xpath()返回的空数组转换成NULL：

ROLLBACK;
BEGIN;
DO $$
DECLARE
  v_xml xml;
  v_record RECORD;
BEGIN
  v_xml := '<?xml version="1.0" encoding="UTF-16"?>
<root>
  <table>
    <row><a>1</a><b>2</b><c>3</c></row>
    <row><a></a><b>5</b><c>6</c></row>
    <row><a>7</a><b></b><c>9</c></row>
  </table>
</root>';

  FOR v_record IN
    SELECT
      -- 空数组转NULL，再转为text类型
      NULLIF(xpath('./a/text()', row_node)::text[], '{}')::text AS a,
      NULLIF(xpath('./b/text()', row_node)::text[], '{}')::text AS b,
      NULLIF(xpath('./c/text()', row_node)::text[], '{}')::text AS c
    FROM unnest(xpath('/root/table/row', v_xml)) AS row_node
  LOOP
    -- 替换成你实际的插入表逻辑，这里先打印验证结果
    RAISE NOTICE 'a: %, b: %, c: %', v_record.a, v_record.b, v_record.c;
  END LOOP;
END $$;
COMMIT;

原理：当XML节点为空（比如<a></a>），xpath('./a/text()', row_node)会返回空数组{}，NULLIF会把这个空数组转换成SQL NULL，完美匹配我们的需求。

方法2：结合数组访问与CASE处理空值 #

如果需要更精细的控制（比如区分节点不存在和节点为空的情况），可以用数组索引+CASE语句：

SELECT
  CASE
    -- 检查节点是否存在值，不存在则返回NULL
    WHEN (xpath('./a/text()', row_node))[1] IS NOT NULL THEN
      (xpath('./a/text()', row_node))[1]::text
    ELSE NULL
  END AS a,
  CASE
    WHEN (xpath('./b/text()', row_node))[1] IS NOT NULL THEN
      (xpath('./b/text()', row_node))[1]::text
    ELSE NULL
  END AS b
FROM unnest(xpath('/root/table/row', v_xml)) AS row_node

这种方式适合需要对不同空值场景做差异化处理的情况。

方法3：用xpath_exists判断节点存在性 #

如果有些节点可能完全不存在（而不是存在但为空），可以用xpath_exists先判断节点是否存在，再处理值：

SELECT
  CASE
    WHEN xpath_exists('./a', row_node) THEN
      NULLIF((xpath('./a/text()', row_node))[1]::text, '')
    ELSE NULL
  END AS a
FROM unnest(xpath('/root/table/row', v_xml)) AS row_node

这样不管节点是不存在还是存在但为空，都会返回SQL NULL。

另外提个小注意：你的XML用了UTF-16编码，处理时要确保PostgreSQL的客户端编码和XML编码兼容，避免出现乱码问题。

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