XML in data, how do display just one tag?

From: SN <throne7_at_my-deja.com>
Date: 7 Nov 2001 06:58:23 -0800
Message-ID: <c2ec2c5b.0111070658.2d16dc0@posting.google.com>

Hello Gurus,

Could you tell me how to do this? I have a table within which there is a huge (1024 chars) column that contains an XML string. I need a query that will query this table for all the rows that has a special tag in this XML string and return the transaction_id and this special tag in the XML only.

One might do this:

select transaction_id,parameter_str from sometable where parameter_str like '<cm_technology>TERAYON PROPRIETARY</cm_technology>'

However, this will pull out the entire parameter_str when all I want to display
is just the transaction_id and the
'<cm_technology>TERAYONPROPRIETARY</cm_technology>' tag.

How can I do this? can u show me?

create table sometable(

TRANSACTION_ID        NOT NULL NUMBER(9)
STATUS                NOT NULL VARCHAR2(1)
ELEMENT_TYPE          NOT NULL VARCHAR2(10)
ELEMENT_SEQ           NOT NULL NUMBER(9)
ACTION                NOT NULL VARCHAR2(1)
PARAMETER_STR         NOT NULL VARCHAR2(1024)
CREATED_DATETIME      NOT NULL DATE
MODIFIED_DATETIME     NOT NULL DATE
CREATED_BY_USERID     NOT NULL CHAR(10)

MODIFIED_BY_USERID NOT NULL CHAR(10)
)

Which has a record as follows

insert into sometable('5000','I','CM','5000','U',
'<?xml version=''1.0''?><transaction
action=''U''><transaction_id>5001</transaction_id><ntd_cm_device_seq>2823082</ntd_cm_device_seq><ISP>SomeISP</ISP><company_number>135</company_number><account_number>452651500</account_number><cm_mac_address>0004752D7D16</cm_mac_address><cm_serial_number is_required=''N''>14140017238</cm_serial_number><cm_ip_address is_required=''N''></cm_ip_address><cm_technology>TERAYON PROPRIETARY</cm_technology><cm_model>UBR7246</cm_model><create_date>10/31/2001</create_date><street_number>83</street_number><street_type

is_required=''N''>St</street_type><street_compass
is_required=''N''></street_compass><street_name>River</street_name><apt_number
is_required=''N''></apt_number><city
is_required=''N''>Toronto</city><province>ON</province><service_area_name>BSMT11</service_area_name><return_segment_name>BSHUB1</return_segment_name><primary_name>Bloor333</primary_name><mac_domain_mac_address>031260000002</mac_domain_mac_address></transaction>',
to_date(sysdate),
to_date(sysdate),

'nms_admin',
'nms_admin')
Received on Wed Nov 07 2001 - 08:58:23 CST