Re: XML in data, how do display just one tag?

SN wrote:
>
> 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''?><!-- CM_Update 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')

You will need to use XPath to get a portion of the doc. I think the XML toolkit for PL/SQL has this in it. The new Oracle mag has several articles concerning XML and the database in it.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.