This discussion is archived
4 Replies Latest reply: Oct 26, 2012 1:34 PM by mdrake RSS

add multiple attribute to a tag in the xml

970697 Newbie
Currently Being Moderated
hi ,

I have an xml_data in the table xml_test_table in the format

<?xml version="1.0"?>
<ROWSET>
<ROW>
<CLASS> XII A </CLASS>
<TEACHER> KATE </TEACHER>
<STUDENT_STRENGTH> 30 </STUDENT_STRENGTH>
<DESKS> 50 </DESKS>
</ROW>
<ROW>
<CLASS>XII B</CLASS>
<STUDENT_STRENGTH> 40 </STUDENT_STRENGTH>
<NUMBER OF GIRLS> 15 </NUMBER OF GIRLS>
<NUMBER_OF_BOYS> 25 </NUMBER_OF_BOYS>
</ROW>
</ ROWSET>

I need to change the xml in this format :

<?xml version="1.0"?>
<ROWSET>
<ROW>
<CLASS > XII A </CLASS>
<TEACHER age="37" subject="Mathematics" > KATE </TEACHER>
<STUDENT_STRENGTH> 30 </STUDENT_STRENGTH>
<DESKS> 50 </DESKS>
</ROW>
<ROW>
<CLASS>XII B</CLASS>
<STUDENT_STRENGTH> 40 </STUDENT_STRENGTH>
<NUMBER OF GIRLS> 15 </NUMBER OF GIRLS>
<NUMBER_OF_BOYS> 25 </NUMBER_OF_BOYS>
</ROW>
</ ROWSET>

Need to add Multiple attribute to all 'teacher' tag in the xml .
i am able to add a single attribute using

select insertChildXML(xml_data, 'ROWSET/ROW/TEACHER ', '@age', '37') from test_xml_table ;

Thanks in advance
  • 1. Re: add multiple attribute to a tag in the xml
    odie_63 Guru
    Currently Being Moderated
    What's your database version?
    SELECT * FROM v$version;
    Do you want to modify the XML document in a SELECT or do you want to update the table?
    In both cases, using insertChildXML() you'll need multiple calls to the function, as you can't add more than one child at a time.

    If you're on 11.2.0.3, you can use XQuery Update to do it in one call.
  • 2. Re: add multiple attribute to a tag in the xml
    970697 Newbie
    Currently Being Moderated
    Hi ,

    I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production .
    In this scenario , do we have a simple search and replace function for XML using which i can replace the tag <TEACHER> with <TEACHER age="37" subject="mathematics"> (I understand its not the correct was of solving this).

    I don't want to convert this into a VARCHAR and then convert it do this operation because i am not sure about the length of the XML .


    Thanks in advance
  • 3. Re: add multiple attribute to a tag in the xml
    odie_63 Guru
    Currently Being Moderated
    In this scenario , do we have a simple search and replace function for XML using which i can replace the tag <TEACHER> with <TEACHER age="37" subject="mathematics"> (I understand its not the correct was of solving this).
    Why not use two separate insertChildXML calls?
    The first one adds the @age attribute, and the second one adds the @subject attribute over the result of the first one.
  • 4. Re: add multiple attribute to a tag in the xml
    mdrake Expert
    Currently Being Moderated
    IN 11.2.0.3.0 you could use XQuery-Update to do this in a single operaiton

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points