Update xml element value in Oracle

Question

Having a XMLTYPE column in an Oracle table, I would like to update the values of some xml elements using UpdateXML method but I have troubles doing so because of a namespace which is applied on an xml element which is not parent. The xml structure of my elmenets looks something like:

<a>
  <b xmlns="urn:www.someSite.com/myModel">
    <c>my value</c>
  </b>
</a>

and an update of the following form it does not work:

UPDATE myTable 
  SET myColumn = UpdateXML(myColumn, '/a/b/c','other value', 'xmlns="urn:www.someSite.com/myModel"');

Can't you change the xml to have at least a shortname for the namespace ie xmlns:ns1="urn:www.someSite.com/myModel" so you could do /a/ns1:b/ns1:c — A.B.Cade
– A.B.Cade, Commented Apr 2, 2012 at 13:39
no, I cannot change the xml, this is the format in which is saved in the database, and it cannot be changed. — axl g
– axl g, Commented Apr 2, 2012 at 13:46

Community · Accepted Answer · 2017-05-23 11:49:02Z

3

Pretty much the same as this post but uglier...

UPDATE myTable
   SET myColumn = updatexml(myColumn ,
                 '/a/*',
                 updatexml(extract(myColumn , '/a/*'),
                           'b/c/text()',
                           'my new value',
                           'xmlns=urn:www.someSite.com/myModel'));

EDIT: If you have more then one b element in a you'll have to change the whole text within a and not for each child, so you can try:

UPDATE myTable
   SET myColumn = updatexml(myColumn ,
                 '/a/text()',
                 updatexml(extract(myColumn , '/a/*'),
                           'b/c/text()',
                           'my new value',
                           'xmlns=urn:www.someSite.com/myModel'));

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Apr 2, 2012 at 13:34

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update xml element value in Oracle

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related