巧用xmltype解析clob資料

對于clob的資料，很多場合中都使用xml的格式，但是對于資料的查取和處理總是感覺力不從心。在條件允許的情況下，如果能夠巧妙的使用xmltype來做資料處理，無意中是對于clob的一個處理利器。

簡單說下需求。

資料庫裡存放的clob類似下面的格式

現在有一個需求是能夠把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('

    '));

來看看xmltype的效果，根據根節點，找到最終的葉子節點。

select extract(xmltype(c_cml),'/ObjectInfo/Relations/RelationInfo/RelatedObjects/RelatedObjectInfo') a,       

    id

from    aa where id=5;      

A                                                                                                          ID

---------------------------------------------------------------------------------------------------- --------

tInfo objID="##PC4.0##118145"/>

"##PC4.0##117696"/>

tedObjectInfo objID="##PC4.0##118583"/>

 objID="##PC4.0##30310"/>

elatedObjectInfo objID="##PC4.0##30318"/>

nfo objID="##PC4.0##30309"/>

可以看到已經查到了RelatedObjectInfo>這部分的資料。

更進一步,把xml标記進行清除。可以直接使用replace

SQL> select replace(extract(xmltype(c_cml),'/ObjectInfo/Relations/RelationInfo/RelatedObjects/RelatedObjectInfo'),'##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'),'',',') a,    

  2           id

  3          from    aa where id=5;

##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的資料格式也是有一些限定的。