巧用xmltype解析clob資料

巧用xmltype解析clob資料

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