extractvalue在处理XMLTYPE类型运算时非常方便，但在处理超过4000字节column就力不从心了。

问题：

一位朋友在Oracle中使用extractvalue处理XMLTYPE类型时，发现column值超过4000字节时就会报错ORA-01706.

是否真会这样呢？

通过实验重现问题：

1.创建XML table

CreateTABLEtestxml(idNUMBER,dataXMLTYPE);

2.创建目录

CREATEORREPLACEDIRECTORYEXPDP_DIRAS'/data/expdp_dir/';

3.在目录/data/expdp_dir/ 中建立11.xml文件，模拟Data2长度为4001字节，文件内容如下：

<Workbook><Row><Data1>MES</Data1><Data2>01234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340123401234012340</Data2></Row></Workbook>

4.通过bfile方式insert数据

insertintotestxml(id,data)values(6,xmltype(bfilename('EXPDP_DIR','11.xml'),nls_charset_id('AL32UTF8')));commit;

5.查看insert后数据

select*fromtestxml

6.使用extractvalue查询小于4000字节column Data1，可以正常返回结果

selectextractvalue(data,'/Workbook/Row/Data1')fromtestxml;

7.使用extractvalue查询大于4000字节column Data2，出现报错ORA-01706

selectextractvalue(data,'/Workbook/Row/Data2')fromtestxml;

以上证实extractvalue无法处理大于4000字节column

解决：

查看Oracle官方文档对extractvalue函数的介绍

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions061.htm#SQLRF06173

The EXTRACTVALUE function is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you use the XMLTABLE function, or the XMLCAST and XMLQUERY functions instead. See XMLTABLE, XMLCAST, and XMLQUERY for more information.

原来官方已建议不要再使用extractvalue，而建议使用XMLTABLE,XMLCASE,XMLQUERY函数

继续查阅XMLTABLE使用文档

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions253.htm#SQLRF06232

利用XMLTABLE函数处理大于4000字节column:

SELECT*FROMtestxml,XMLTABLE('/Workbook'PASSINGtestxml.dataCOLUMNSCONclobPATH'/Workbook/Row/Data2')

在XMLTABLE可以自定义字符类型，这里指定clob类型可成功获取超过4000字节数据