How to extract or Update xml attribute values in oracle

Question

Table name: conn_details I have this xml which stored in CLOB column(conn_param) in oracle.

<Parameters>
   <Parameter Name="USER" Value="my_user"/>
   <Parameter Name="PASSWORD" Value="my_password"/>
   <Parameter Name="HOST" Value="google.com"/>
</Parameters>

I am looking for the simple SQL queries which will get value or will update it.

For example: I would like to get a HOST value and the result will be: google.com I would like to update attribute PASSWORD with value my_new_password and the result will be:

<Parameters>
   <Parameter Name="USER" Value="my_user"/>
   <Parameter Name="PASSWORD" Value="my_new_password"/>
   <Parameter Name="HOST" Value="google.com"/>
</Parameters>

So, I am looking something simple that I will provide some kind XPath in order to achieve my needs.

Answer 1

To get relevant value use this SQL:

SELECT extract(xmltype(conn_param), '/Parameters/Parameter[@Name="HOST"]/@Value') 
  FROM conn_details;

To update xml value use this SQL:

UPDATE conn_details SET conn_param =   UPDATEXML(xmltype(conn_param),
   '/Parameters/Parameter[@Name="HOST"]/@Value',to_char('google.com')).getClobVal()

Answer 2

First you have to convert the CLOB into an XMLType, for example like this:

create table t (p CLOB) STORE P as ....;

insert into t values ('<Parameters>
   <Parameter Name="USER" Value="my_user"/>
   <Parameter Name="PASSWORD" Value="my_password"/>
   <Parameter Name="HOST" Value="google.com"/>
</Parameters>');

insert into t values ('<Parameters>
   <Parameter Name="USER" Value="my_user_2"/>
   <Parameter Name="PASSWORD" Value="my_password_2"/>
   <Parameter Name="HOST" Value="google.com_2"/>
</Parameters>');


create view v as 
select xmlparse(DOCUMENT p WELLFORMED) as XML_doc
from t;

Then you can select it like this:

create view xml_scalar as 
SELECT 
   user_value, password_value, host_value, XML_doc
   FROM xml_view natural join
   XMLTABLE('/Parameters'
      PASSING xml_view.XML_doc
      COLUMNS 
         user_value varchar2(60) PATH 'Parameter[@Name="USER"]/@Value',
         password_value varchar2(60) PATH 'Parameter[@Name="PASSWORD"]/@Value',
         host_value varchar2(60) PATH 'Parameter[@Name="HOST"]/@Value')   
      x;

select user_value, password_value, host_value from  xml_scalar;       


| USER_VALUE | PASSWORD_VALUE |   HOST_VALUE |
|------------|----------------|--------------|
|    my_user |    my_password |   google.com |
|  my_user_2 |  my_password_2 | google.com_2 |

Then you should be able to make updates like this:

UPDATE xml_scalar
SET XML_doc =
   UPDATEXML(XML_doc,
   'Parameters/Parameter[@Name="PASSWORD"]/@Value', 'secret')
where user_value = 'my_user';