Oracle处理XML字段时遇到的ORA-31013: XPATH 表达式无效问题 

1 select extractValue(ed.info_id, '/Root/ExpandProfile/PhoneNumber') as phone,
2 extractValue(ed.info_id, '/Root/ExpandProfile/400ServicePhone') as servicephone,
3 extractValue(ed.info_id, '/Root/ExpandProfile/FaxNumber') as fax,
4 extractValue(ed.info_id, '/Root/ExpandProfile/MarketingQQ') as qq,
5 extractValue(ed.info_id, '/Root/ExpandProfile/WechatNumber') as wechat,
6 extractValue(ed.info_id, '/Root/ExpandProfile/Mail') as mail 
7 from entity_detail ed
8 where ed.info_rel_type = ''
9 and ed.info_rel_id = ''
View Code

这是我的sql语句，粗略一看没有什么大问题

排查方法，中间的 extractValue函数一句一句注释掉运行

最后的结果是，oracle，xml标签里面不能存在数字，否则会报错。

sql修改过后就可以正常运行了

select extractValue(ed.info_id, '/Root/ExpandProfile/PhoneNumber') as phone,
extractValue(ed.info_id, '/Root/ExpandProfile/ServicePhone') as servicephone,
extractValue(ed.info_id, '/Root/ExpandProfile/FaxNumber') as fax,
extractValue(ed.info_id, '/Root/ExpandProfile/MarketingQQ') as qq,
extractValue(ed.info_id, '/Root/ExpandProfile/WechatNumber') as wechat,
extractValue(ed.info_id, '/Root/ExpandProfile/Mail') as mail 
from entity_detail ed
where ed.info_rel_type = ''
and ed.info_rel_id = ''

 

posted @ 2018-03-29 14:55  OnceEnough  阅读(463)  评论(0)  编辑  收藏  举报