ORACLE sql Developer 解析XML

[gbcin]

我的表里面有个字段是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小白求指导，多谢

[shiyiwan]

你需要先将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  <Configurations>
  4    <Configuration SpeedEnv="Local">
  5      <Server>
  6        <IDSInvokeUrl value="http://localhost:9080/speed" />
  7      </Server>
  8    </Configuration>
  9  </Configurations>'
 10  /
 
2 rows updated
 
SQL> SELECT EXTRACT(XMLTYPE(col2),'/Configurations/Configuration/Server/IDSInvokeUrl') FROM idletest;
 
EXTRACT(XMLTYPE(COL2),'/CONFIG
--------------------------------------------------------------------------------
<IDSInvokeUrl value="http://localhost:9080/speed"/>
<IDSInvokeUrl value="http://localhost:9080/speed"/>