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

Replace multiple tags in the xml with a new ones

970697 Newbie
Currently Being Moderated
hi ,

I have an xml_data in the table test_xml_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>

Need to replace some tag with a new tag (<TEACHER> with <CLASS_TEACHER> and <CLASS> with <CLASS_AND_DIVISION>), currently the method used is
  select regexp_replace(regexp_replace(xml_data,'TEACHER>','CLASS_TEACHER>'),'CLASS>','CLASS_AND_DIVISION>')from test_xml_table ;
database version
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE     11.2.0.2.0     Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production


The issues I am facing here is that this is returning me a string which I have to convert to XML again and having to use
the regexp_replace function0 multiple times . Is there a XML function using which can be used do this , and if there is
no other option like that, is there any alternative replace function which can be used replace all the required tags in a single go ?

Thanks in advance

Edited by: Mr. 13 on Oct 26, 2012 1:40 AM
  • 1. Re: Replace multiple tags in the xml with a new ones
    odie_63 Guru
    Currently Being Moderated
    A possible way using XSLT :
    SQL> select xmltransform(xml_data
      2         , xmlparse(document
      3  '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      4    <xsl:template match="@*|node()">
      5      <xsl:copy>
      6        <xsl:apply-templates select="@*|node()"/>
      7      </xsl:copy>
      8    </xsl:template>
      9    <xsl:template match="TEACHER">
     10      <CLASS_TEACHER>
     11        <xsl:apply-templates select="@*|node()"/>
     12      </CLASS_TEACHER>
     13    </xsl:template>
     14    <xsl:template match="CLASS">
     15      <CLASS_AND_DIVISION>
     16        <xsl:apply-templates select="@*|node()"/>
     17      </CLASS_AND_DIVISION>
     18    </xsl:template>
     19  </xsl:stylesheet>')
     20  ) as result
     21  from test_xml_table
     22  ;
     
    RESULT
    --------------------------------------------------------------------------------
     
    <ROWSET>
     <ROW>
      <CLASS_AND_DIVISION> XII A </CLASS_AND_DIVISION>
      <CLASS_TEACHER> KATE </CLASS_TEACHER>
      <STUDENT_STRENGTH> 30 </STUDENT_STRENGTH>
      <DESKS> 50 </DESKS>
     </ROW>
     <ROW>
      <CLASS_AND_DIVISION>XII B</CLASS_AND_DIVISION>
      <STUDENT_STRENGTH> 40 </STUDENT_STRENGTH>
      <NUMBER_OF_GIRLS> 15 </NUMBER_OF_GIRLS>
      <NUMBER_OF_BOYS> 25 </NUMBER_OF_BOYS>
     </ROW>
    </ROWSET>
     
  • 2. Re: Replace multiple tags in the xml with a new ones
    mdrake Expert
    Currently Being Moderated
    You should NEVER manipulate XML as a string. XML is not a string of text with some rangle angled brackets it, is a complex object model that can be represented as text with angled brackets it it. Any attempt to perform string manipulation on serialized XML will end in tears. You have been warned..

    That said, as has been pointed out, you are trying perform a transformation of the document, you should use XSLT or XQUERY for that purpose.

Legend

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