Query data in Oracle XMLType

XMLtype query operations:
Example of the query object: Table Name: etab. The first column is varchar2 () and the second column is xmltype,
The id field value is aa.
The value of the xmlcol field is:
[Html]
<ProductMetaData>
<SatelliteID> 02B </SatelliteID>
<ReceiveStationID> BRA </ReceiveStationID>
<SensorID aa = 'exattr'> CCD </SensorID>
<ReceiveTime> 22:12:24 </ReceiveTime>
<OrbitID> 448 </OrbitID>
</ProductMetaData>

 
1: extract
Purpose: extract the value of the specified node in the XML string (including the tag at both ends of the node). The result is still in clob format.
Use the syntax of column. extract ('// xxx/xx') or extract (column,' // xxx/xx ').
For example:
Select t. id, t. xmlcol. extract ('// ProductMetaData/sensorid') from etab t
Select t. id, extract (t. xmlcol, '// ProductMetaData/sensorid') from etab t
Extracted content is (still in CLOB format): <SensorID aa = 'exattr '> BRA </SensorID>
 
You can also add/text () to the end to obtain the internal text (the result is still in clob format)
For example:
Select t. id, t. xmlcol. extract ('// ProductMetaData/SensorID/text ()') from etab t
Extracted content is (still in CLOB format): BRA
 
Or Add/@ att to get the node attribute value.
For example:
Select t. id, t. xmlcol. extract (www.2cto.com) from etab t
Extracted content is (still in CLOB format): ExAttr
 
You can use the getnumberval () or getstringval () function to convert the result to a required text or number.
For example:
Select t. id, t. xmlcol. extract ('// ProductMetaData/SensorID/text ()'). getstringval () from etab t
Extracted content is (Text Format): BRA
 
Select t. id, t. xmlcol. extract ('// ProductMetaData/OrbitID/text ()'). getnumberval () from etab t
Extracted content is (number format, which can be used for condition determination, for example, greater than or less than): 448
 
Extract content as condition judgment:
Select t. id from etab t where t. xmlcol. extract ('// ProductMetaData/OrbitID/text ()'). getnumberval ()> 440
 
2. extactrvalue
Purpose: extract the value of the specified node in the XML string (excluding the tag at both ends of the node). The result is in text format (if the value is a number, it may also be considered as a number, it can also be used for condition determination in queries)
Only the format of extractvalue (column, '// xxx/xx') can be used
For example:
Select t. id, extractvalue (t. xmlcol, '// ProductMetaData/sensorid') from etab t
Extracted content is the PMS in text format.

Select t. id from etab t where t. xmlcol. extract ('// ProductMetaData/OrbitID/text ()'). getnumberval ()> 2440
 

Author: rrrrssss00