SQL XMLTABLE 与解析技巧

xmltable返回空结果的主因是命名空间未声明或路径不匹配；必须显式声明命名空间、使用绝对路径、确保类型可转换，且避免在where中嵌套调用。

XMLTABLE 解析失败：返回空结果的常见原因

不是 XML 写得不标准，而是 XMLTABLE 对输入要求极严格——它只接受合法的、带明确命名空间声明的 XML 文档片段，且路径表达式必须完全匹配节点层级与命名空间前缀。

• XMLTABLE 不会自动忽略空白、换行或注释；如果源 XML 是字符串拼接生成的，CHR(10) 或多余空格可能导致解析中断
• 命名空间没在 XMLNAMESPACES 中声明，或声明了但 XPath 里没用对应前缀，节点就“不可见”
• 用 //item 这类模糊路径时，postgresql 的 XMLTABLE 默认不支持 XPath 2.0，只认绝对路径或简单相对路径（如 item）
• 输入是单个元素（如 <name>Alice</name>），但 COLUMNS 定义了多行映射，会导致零行输出

PostgreSQL 中正确写法：命名空间 + 路径 + 类型对齐

命名空间不是可选项，是硬性前提；路径要从根节点开始写清楚；列类型必须和实际文本内容能隐式转换，否则该列值为 NULL。

• 用 XMLNAMESPACES('http://example.com' AS ex) 声明前缀，XPath 中必须写成 /ex:root/ex:item
• 如果 XML 没有命名空间，也要显式声明空命名空间：XMLNAMESPACES('' AS "")，然后路径写成 /root/item
• COLUMNS name TEXT PATH 'name', age int PATH 'age' —— 这里的 PATH 是相对于当前行节点的子路径，不是全文路径
• 若某节点可能缺失，加 default NULL 或具体默认值，避免整行被跳过

select * FROM XMLTABLE(   XMLNAMESPACES('' AS ""),   '/items/item'   PASSING xmlparse(DOCUMENT '<items><item><name>Bob</name><age>30</age></item></items>')   COLUMNS     name TEXT PATH 'name',     age  INT  PATH 'age' );

性能陷阱：别在 WHERE 里嵌套 XMLTABLE

每次调用 XMLTABLE 都会触发完整 XML 解析，如果把它放在子查询或 JOIN 条件中反复执行，I/O 和 CPU 开销会指数级上升。

• 先用 xmlparse() 把字符串转为 xml 类型存到临时表，再统一解析，比在大表上每行都调一次 XMLTABLE 快 5–10 倍
• 避免在 WHERE 中写 (SELECT ... FROM XMLTABLE(...)) = 'x' —— 这会让优化器无法下推条件，全表扫描不可避免
• 如果只查某个字段，优先用 xpath() 提取单值，比启动整个 XMLTABLE 轻量得多，例如：xpath('//name/text()', doc)[1]

XMLTABLE vs xpath()：什么情况该换函数

XMLTABLE 是为了结构化展开，xpath() 是为了快速抽字段；混用反而增加理解成本和维护难度。

• 需要把 XML 展成多行多列（比如订单含多个商品），必须用 XMLTABLE
• 只是判断某个节点是否存在，或提取一个固定路径的值，用 xpath() 更直接，返回 xml[]，取第一个元素即可：(xpath('//status/text()', doc))[1]
• xpath() 支持更灵活的谓词（如 //item[price > 100]），而 XMLTABLE 的 PATH 不支持表达式过滤
• 注意 xpath() 返回的是字节数组，要转文本得套一层 encode() 或 convert_from()，容易漏掉这步导致乱码

实际用的时候，最常卡住的不是语法，是命名空间声明和路径层级错了一级——XML 看着一样，XPath 少个 / 或多一个 text()，结果就是空。