4 Replies Latest reply: Oct 26, 2012 3:34 PM by mdrake RSS

    add multiple attribute to a tag in the xml

    970697
      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
          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
            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
              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
                IN 11.2.0.3.0 you could use XQuery-Update to do this in a single operaiton