公众号：杨建荣的学习笔记 · 作者：r2笔记33天 · 发布：2014-07-14 18:46:18 · 原文链接

对于clob的数据，很多场合中都使用xml的格式，但是对于数据的查取和处理总是感觉力不从心。在条件允许的情况下，如果能够巧妙的使用xmltype来做数据处理，无意中是对于clob的一个处理利器。
简单说下需求。
数据库里存放的clob类似下面的格式
<ObjectInfo xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<Relations>
<RelationInfo relationType=”BackReference”>
<RelatedObjects>
<RelatedObjectInfo objID=”##PC4.0##118146″ />
<RelatedObjectInfo objID=”##PC4.0##30369″ />
<RelatedObjectInfo objID=”##PC4.0##118145″ />
<RelatedObjectInfo objID=”##PC4.0##118211″ />
<RelatedObjectInfo objID=”##PC4.0##117696″ />
<RelatedObjectInfo objID=”##PC4.0##119094″ />
<RelatedObjectInfo objID=”##PC4.0##118203″ />
<RelatedObjectInfo objID=”##PC4.0##118133″ />
<RelatedObjectInfo objID=”##PC4.0##118135″ />
<RelatedObjectInfo objID=”##PC4.0##118583″ />
<RelatedObjectInfo objID=”##PC4.0##30313″ />
<RelatedObjectInfo objID=”##PC4.0##30310″ />
<RelatedObjectInfo objID=”##PC4.0##110154″ />
<RelatedObjectInfo objID=”##PC4.0##30317″ />
<RelatedObjectInfo objID=”##PC4.0##30314″ />
<RelatedObjectInfo objID=”##PC4.0##30315″ />
<RelatedObjectInfo objID=”##PC4.0##30318″ />
<RelatedObjectInfo objID=”##PC4.0##118131″ />
<RelatedObjectInfo objID=”##PC4.0##30309″ />
<RelatedObjectInfo objID=”##PC4.0##118160″ />
<RelatedObjectInfo objID=”##PC4.0##119101″ />
</RelatedObjects>
</RelationInfo>
</Relations>
</ObjectInfo>

现在有一个需求是能够把RelatedObjectInfo 中的objID查取，整理后得到一个以逗号分隔的串。
比如上面的clob数据，需要输出成为下面的形式：
##PC4.0##118146,##PC4.0##30369,##PC4.0##118145,##PC4.0##118211,##PC4.0##117696,##PC4.0##119094,##PC45.0##118203,

如果直接通过sql语句来写，确实很难实现，如果通过Pl/sql也需要做不少的工作。
下面尝试使用xmltype来直接读取clob数据。
简单创建一个测试表，插入数据。
create table AA(id number,c_cml clob);
insert into aa values(5,to_clob(‘
<ObjectInfo xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<Relations>
<RelationInfo relationType=”BackReference”>
<RelatedObjects>
<RelatedObjectInfo objID=”##PC4.0##118146″ />
<RelatedObjectInfo objID=”##PC4.0##30369″ />
<RelatedObjectInfo objID=”##PC4.0##118145″ />
<RelatedObjectInfo objID=”##PC4.0##118211″ />
<RelatedObjectInfo objID=”##PC4.0##117696″ />
<RelatedObjectInfo objID=”##PC4.0##119094″ />
<RelatedObjectInfo objID=”##PC4.0##118203″ />
<RelatedObjectInfo objID=”##PC4.0##118133″ />
<RelatedObjectInfo objID=”##PC4.0##118135″ />
<RelatedObjectInfo objID=”##PC4.0##118583″ />
<RelatedObjectInfo objID=”##PC4.0##30313″ />
<RelatedObjectInfo objID=”##PC4.0##30310″ />
<RelatedObjectInfo objID=”##PC4.0##110154″ />
<RelatedObjectInfo objID=”##PC4.0##30317″ />
<RelatedObjectInfo objID=”##PC4.0##30314″ />
<RelatedObjectInfo objID=”##PC4.0##30315″ />
<RelatedObjectInfo objID=”##PC4.0##30318″ />
<RelatedObjectInfo objID=”##PC4.0##118131″ />
<RelatedObjectInfo objID=”##PC4.0##30309″ />
<RelatedObjectInfo objID=”##PC4.0##118160″ />
<RelatedObjectInfo objID=”##PC4.0##119101″ />
</RelatedObjects>
</RelationInfo>
</Relations>
</ObjectInfo>
‘));

来看看xmltype的效果，根据根节点，找到最终的叶子节点。
select extract(xmltype(c_cml),’/ObjectInfo/Relations/RelationInfo/RelatedObjects/RelatedObjectInfo’) a,
id
from aa where id=5;

A ID
—————————————————————————————————- ——–
<RelatedObjectInfo objID=”##PC4.0##118146″/><RelatedObjectInfo objID=”##PC4.0##30369″/><RelatedObjec 5
tInfo objID=”##PC4.0##118145″/><RelatedObjectInfo objID=”##PC4.0##118211″/><RelatedObjectInfo objID=
“##PC4.0##117696″/><RelatedObjectInfo objID=”##PC4.0##119094″/><RelatedObjectInfo objID=”##PC4.0##11
8203″/><RelatedObjectInfo objID=”##PC4.0##118133″/><RelatedObjectInfo objID=”##PC4.0##118135″/><Rela
tedObjectInfo objID=”##PC4.0##118583″/><RelatedObjectInfo objID=”##PC4.0##30313″/><RelatedObjectInfo
objID=”##PC4.0##30310″/><RelatedObjectInfo objID=”##PC4.0##110154″/><RelatedObjectInfo objID=”##PC4
.0##30317″/><RelatedObjectInfo objID=”##PC4.0##30314″/><RelatedObjectInfo objID=”##PC4.0##30315″/><R
elatedObjectInfo objID=”##PC4.0##30318″/><RelatedObjectInfo objID=”##PC4.0##118131″/><RelatedObjectI
nfo objID=”##PC4.0##30309″/><RelatedObjectInfo objID=”##PC4.0##118160″/><RelatedObjectInfo objID=”##
PC4.0##119101″/>

可以看到已经查到了<RelatedObjectInfo>这部分的数据。

更进一步,把xml标记进行清除。可以直接使用replace
SQL> select replace(extract(xmltype(c_cml),’/ObjectInfo/Relations/RelationInfo/RelatedObjects/RelatedObjectInfo’),'<RelatedObjectInfo objID=”‘,”) a,
2 id
3 from aa where id=5;

A ID
—————————————————————————————————- ——–
##PC4.0##118146″/>##PC4.0##30369″/>##PC4.0##118145″/>##PC4.0##118211″/>##PC4.0##117696″/>##PC4.0##11 5
9094″/>##PC4.0##118203″/>##PC4.0##118133″/>##PC4.0##118135″/>##PC4.0##118583″/>##PC4.0##30313″/>##PC
4.0##30310″/>##PC4.0##110154″/>##PC4.0##30317″/>##PC4.0##30314″/>##PC4.0##30315″/>##PC4.0##30318″/>#
#PC4.0##118131″/>##PC4.0##30309″/>##PC4.0##118160″/>##PC4.0##119101″/>

然后直接清除尾部标记。
SQL> select replace(replace(extract(xmltype(c_cml),’/ObjectInfo/Relations/RelationInfo/RelatedObjects/RelatedObjectInfo’),'<RelatedObjectInfo objID=”‘,”),'”/>’,’,’) a,
2 id
3 from aa where id=5;

A ID
—————————————————————————————————- ——–
##PC4.0##118146,##PC4.0##30369,##PC4.0##118145,##PC4.0##118211,##PC4.0##117696,##PC4.0##119094,##PC4 5
.0##118203,##PC4.0##118133,##PC4.0##118135,##PC4.0##118583,##PC4.0##30313,##PC4.0##30310,##PC4.0##11
0154,##PC4.0##30317,##PC4.0##30314,##PC4.0##30315,##PC4.0##30318,##PC4.0##118131,##PC4.0##30309,##PC
4.0##118160,##PC4.0##119101,
这样就能很快实现需求，把clob的数据当做xml来做处理，当然了对于clob的数据格式也是有一些限定的。