Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UpdateXML Where condition...

229023Oct 23 2006 — edited Oct 24 2006

How to use where condition to update only one particular row. For example, i've this table xmlsun3 which i created using the sample table employees in the HR schema

DECLARE
  l_xmltype XMLTYPE;
BEGIN
  SELECT sys_xmlagg(
           xmlelement(
             "EMP",
             xmlforest(e.employee_id, e.first_name,e.last_name,  e.job_id, e.manager_id, e.hire_date, e.salary, e.commission_pct)
           )
         )
  INTO   l_xmltype
  FROM   employees e;

  INSERT INTO xmlsun3 VALUES (l_xmltype);
  COMMIT;
END;
/

Now if i want to update salary of one particular row with first name as Steven, i tried the following statement

update xmlsun3 x
set value(x) =  updatexml(value(x),'/ROWSET/EMP/SALARY/text()','24500')
where extract(value(x),'/ROWSET/EMP/FIRST_NAME/text()').getStringVal() = 'Steven';

But it updates all the rows with the value 24500. I tried using existsnode, but the same result. How to update this one particular record?

Thanks,
Sundar

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 21 2006
Added on Oct 23 2006
2 comments
860 views