PostgreSQL函数报错：invalid XPath expression问题排查求助

尝试读取XML数据插入临时表，再通过PostgreSQL函数返回结果时，触发“invalid XPath expression”错误。需求是将XML中每个SolutionRatingEntity标签的子元素作为一行数据返回，简化后的XML及函数代码如下：

create or replace function xml()
returns table(Id integer
,CriteriaName character varying
,Level INTeger
,SolutionName character varying     
,ParentName character varying     
,SuperParentName character varying     
,Rating character varying     
,InScopeFlag INTEGER    
,BusinessPriority character varying     
,NodeNumber character varying)
as $$
declare
xmlvalue text := '<ArrayOfSolutionRatingEntity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SolutionRatingEntity>
<Id>0</Id>
<IsActive>true</IsActive><LastModifiedBy>SYSTEM</LastModifiedBy><LastModifiedOn>2022-09-30</LastModifiedOn>
<CriteriaName /><SolutionName>NAme</SolutionName><ParentName /><Level>0</Level><SolutionRating /><SuperParentName />
<InScopeFlag>false</InScopeFlag><IsProcessNode>false</IsProcessNode></SolutionRatingEntity></ArrayOfSolutionRatingEntity>';
begin
drop if exists tempCriteiaRating ;
CREATE TEMPORARY TABLE tempCriteiaRating (    
Id integer NOT NULL  GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 )
,CriteriaName character varying
,Level INTeger
,SolutionName character varying     
,ParentName character varying     
,SuperParentName character varying     
,Rating character varying     
,InScopeFlag INTEGER    
,BusinessPriority character varying     
,NodeNumber character varying     
);

with data as ( 
select xmlvalue::xml val)
INSERT INTO tempCriteiaRating (    
CriteriaName    
,Level    
,SolutionName    
,ParentName    
,SuperParentName    
,Rating    
,InScopeFlag    
,BusinessPriority    
,NodeNumber    
)    
SELECT 
d.CriteriaName    
,d.Level    
,d.SolutionName    
,d.ParentName    
,d.SuperParentName    
,d.Rating    
,d.InScopeFlag    
,d.BusinessPriority    
,d.NodeNumber       
FROM data x,
XMLTABLE('/ArrayOfSolutionRatingEntity/SolutionRatingEntity'
PASSING val
COLUMNS
CriteriaName character varying PATH 'CriteriaName',
Level integer PATH 'level',
SolutionName integer PATH 'Solution Name',
ParentName character varying PATH 'Parent Name',
SuperParentName character varying PATH 'SuperParent Name',
Rating character varying PATH 'Solution Rating',
InScopeFlag INTEGER PATH 'InScopeFlag',
BusinessPriority character varying PATH 'BusinessPriority',
NodeNumber character varying PATH 'NodeNumber') as d;

return query
select * from tempCriteiaRating ;
end;
$$
language plpgsql

• XPath路径包含非法空格：XML标签名采用驼峰式命名（如SolutionName、ParentName），但代码中XPath写成带空格的Solution Name、Parent Name，违反XML标签命名规则，直接导致无效XPath表达式。
• XPath大小写不匹配：XML中标签为<Level>，但代码里XPath写的是小写level，PostgreSQL的XPath区分大小写，无法匹配对应节点。
• 数据类型不匹配：SolutionName在XML中是字符串类型，但代码里定义为integer，即使XPath正确也会触发类型转换错误。
• DROP TABLE语法错误：原代码drop if exists tempCriteiaRating ;缺少TABLE关键字，正确写法为DROP TABLE IF EXISTS tempCriteiaRating;。

create or replace function xml()
returns table(
    Id integer,
    CriteriaName character varying,
    Level integer,
    SolutionName character varying,
    ParentName character varying,
    SuperParentName character varying,
    Rating character varying,
    InScopeFlag integer,
    BusinessPriority character varying,
    NodeNumber character varying
)
as $$
declare
xmlvalue text := '<ArrayOfSolutionRatingEntity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SolutionRatingEntity>
<Id>0</Id>
<IsActive>true</IsActive><LastModifiedBy>SYSTEM</LastModifiedBy><LastModifiedOn>2022-09-30</LastModifiedOn>
<CriteriaName /><SolutionName>NAme</SolutionName><ParentName /><Level>0</Level><SolutionRating /><SuperParentName />
<InScopeFlag>false</InScopeFlag><IsProcessNode>false</IsProcessNode></SolutionRatingEntity></ArrayOfSolutionRatingEntity>';
begin
-- 修正DROP TABLE语法
DROP TABLE IF EXISTS tempCriteiaRating;
CREATE TEMPORARY TABLE tempCriteiaRating (    
    Id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY (INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),
    CriteriaName character varying,
    Level integer,
    SolutionName character varying,
    ParentName character varying,
    SuperParentName character varying,
    Rating character varying,
    InScopeFlag integer,
    BusinessPriority character varying,
    NodeNumber character varying
);

with data as ( 
    select xmlvalue::xml val
)
INSERT INTO tempCriteiaRating (    
    CriteriaName,
    Level,
    SolutionName,
    ParentName,
    SuperParentName,
    Rating,
    InScopeFlag,
    BusinessPriority,
    NodeNumber
)    
SELECT 
    d.CriteriaName,
    d.Level,
    d.SolutionName,
    d.ParentName,
    d.SuperParentName,
    d.Rating,
    d.InScopeFlag,
    d.BusinessPriority,
    d.NodeNumber       
FROM data x,
XMLTABLE('/ArrayOfSolutionRatingEntity/SolutionRatingEntity'
PASSING val
COLUMNS
    CriteriaName character varying PATH 'CriteriaName',
    -- 修正大小写，匹配XML中的<Level>
    Level integer PATH 'Level',
    -- 修正类型为character varying，匹配XML中的字符串
    SolutionName character varying PATH 'SolutionName',
    -- 修正XPath，去掉空格，匹配XML中的<ParentName>
    ParentName character varying PATH 'ParentName',
    -- 修正XPath，去掉空格，匹配XML中的<SuperParentName>
    SuperParentName character varying PATH 'SuperParentName',
    -- 修正XPath，去掉空格，匹配XML中的<SolutionRating>
    Rating character varying PATH 'SolutionRating',
    InScopeFlag integer PATH 'InScopeFlag',
    BusinessPriority character varying PATH 'BusinessPriority',
    NodeNumber character varying PATH 'NodeNumber'
) as d;

return query
select * from tempCriteiaRating;
end;
$$
language plpgsql

• 执行修正后的函数时，InScopeFlag会将XML中的false自动转换为0，符合PostgreSQL布尔值转整数的规则（true→1，false→0）。
• 临时表的自增ID会自动生成，无需手动赋值。

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