Clever Use of xmltype to parse clob data

Clever Use of xmltype to parse clob data

For clob data, xml format is used in many cases, but data query and processing is always insufficient. When conditions permit, if xmltype can be cleverly used for data processing, it is accidentally a powerful tool for clob processing.
Briefly describe the requirements.
The clob stored in the database is similar to the following format:
<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>
 
One requirement is to query the objID in RelatedObjectInfo and sort it out to get a comma-separated string.
For example, the preceding clob data must be output in the following format:
# PC4.0 #118146, ## PC4.0 #30369, ## PC4.0 #118145, ## PC4.0 #118211, ## PC4.0 #117696, # PC4.0 ## 119094, ## PC45.0 #118203,
 
If you use SQL statements directly, it is really difficult to implement it. If you use Pl/SQL, you need to do a lot of work.
Next we try to use xmltype to directly read clob data.
Create a test table and insert data.
Create table AA (id number, c_crf 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>
'));
 
Let's take a look at the xmltype effect and find the final leaf node based on the root node.
Select extract (xmltype (c_crf), '/ObjectInfo/Relations/RelationInfo/RelatedObjects/RelatedObjectInfo'),
Id
From aa where id = 5;
 
A ID
Certificate ------------------------------------------------------------------------------------------------------------
<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 "/>
 
You can see that the <RelatedObjectInfo> data has been found.
 
Further, clear the xml tag. Replace can be used directly.
SQL> select replace (extract (xmltype (c_ncms), '/ObjectInfo/Relations/RelationInfo/RelatedObjects/response'),' <RelatedObjectInfo objID = "',''),
2 id
3 from aa where id = 5;
 
A ID
Certificate ------------------------------------------------------------------------------------------------------------
# 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"/>
 
Then, clear the tail tag directly.
SQL> select replace (extract (xmltype (c_crf), '/ObjectInfo/Relations/RelationInfo/RelatedObjects/response'),' <RelatedObjectInfo objID = "',''), '"/>', ','),
2 id
3 from aa where id = 5;
 
A ID
Certificate ------------------------------------------------------------------------------------------------------------
# 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,

In this way, the demand can be quickly realized, and the data of clob can be processed as xml. Of course, there are some restrictions on the data format of clob.