• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1698
  • Last Modified:

insert/update xmltype attribute or element in plsql stored procedure

hi all,

1. how do i insert an element with attribute tag in xmltype column.

for eg. i have this document in some table with xmltype column.  

<people>
     <person name="johnny" age="10">
         <person name="mandy" age="11">
              "<i want to update/insert new elements here>"
         </person>
     </person>
</people>

and

2. update the document only on attributes, let's say i identified that i want to update person "johnny" age to 15, how am i gonna do that in PL/SQL?

please advise,
wesley
0
wxII
Asked:
wxII
  • 7
  • 4
1 Solution
 
sdstuberCommented:
1.

from your example it looks like you want to nest each person's node,  so your new node will be a child of mandy,
if so,  use this...

update yourtable
set yourxml = insertchildxml(yourxml,'//person[@name="mandy"]','person',xmltype('<person name="wxII" age="99"></person>'))


2.
update yourtable set yourxml = updatexml(yourxml,'//person[@name="johnny"]/@age',15)
0
 
wxIIAuthor Commented:
hi, thanks for the reply, the solution 1 works great but solution 2 throws this error.


ORA-00600: internal error code, arguments: [qmcxeUpdateXml:2.1], [], [], [],[], [], [], []

any other alternatives? please advise and thanks

wxII
0
 
sdstuberCommented:
See metalink doc NOTE:745282.1

there is a patch for it
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
wxIIAuthor Commented:
hi sdstuber,

i couldn't find the metalink you provided in oracle site.



0
 
sdstuberCommented:
How did you search?

Do a Doc ID/ Article ID search for 745282.1.

0
 
sdstuberCommented:
Or you can go directly to the patch...

Patch# 7478006
0
 
wxIIAuthor Commented:
hi sdstuber,

the oracle version i'm using is 11.1.0.6, is it patched in 11.1.0.7? or only available from oracle metalink?

thanks,
wxII
0
 
sdstuberCommented:
yes you must download the patch from metalink

11.1.0.7  still has the problem, and has the same patch to correct it.

If you don't want to install the patch, you'll have to wait until 11.1.0.8 comes out
but that also will only be available via metalink.
0
 
wxIIAuthor Commented:
thanks for the feedback and thorough solutions.
0
 
sdstuberCommented:
glad I could help
0
 
sdstuberCommented:
Do you need additional help on your other open question?
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now