ORACLE sql Developer 解析XML
我的表里面有个字段是CLOB类型,里面是XML格式的数据。
现在想解析出XML里面某几个字段的数据并显示出来。
我找了下网上的资料,说是可以select的时候来extract
select xml.extract('\\Name') from table
但遇到下面的报错:
ORA-22806: not an object or REF
22806. 00000 - "not an object or REF"
*Cause: An attempt was made to extract an attribute from an item that is
neither an object nor a REF.
*Action: Use an object type or REF type item and retry the operation.
Error at Line: 112 Column: 55
oracle小白求指导,多谢
------解决方案--------------------
你需要先将CLOB类型转换成XMLTYPE,之后再应用函数操作,比如:
SQL> desc idletest;
Name Type Nullable Default Comments
---- ------ -------- ------- --------
NM NUMBER Y
COL2 CLOB Y
SQL>
SQL> UPDATE idletest
2 SET col2 = '<?xml version="1.0" encoding="utf-8" ?>
3
4
5
6
7
8
9 '
10 /
2 rows updated
SQL> SELECT EXTRACT(XMLTYPE(col2),'/Configurations/Configuration/Server/IDSInvokeUrl') FROM idletest;
EXTRACT(XMLTYPE(COL2),'/CONFIG
--------------------------------------------------------------------------------