2 Replies Latest reply: Oct 26, 2012 3:34 PM by mdrake-Oracle RSS

    Replace multiple tags in the xml with a new ones

    970697
      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
          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-Oracle
            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.