Query data in Oracle XMLType

Query operation in Oracle XMLtype: query object example: Table Name: etab, first column varchar2 () Name: id, second column: xmltype column name: xmlcol, one row

Query operation in Oracle XMLtype: query object example: Table Name: etab, first column varchar2 () Name: id, second column: xmltype column name: xmlcol, one row

Query the XMLtype in Oracle:

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:

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 ): BRA

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 ('// ProductMetaData/SesorID/@ A') 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