5 Replies Latest reply: Apr 15, 2012 10:20 AM by Marco Gralike RSS

    how to eliminate root element

    912973
      HI
      Iam having the following messagetext from one of the column which is of type XmlType.I wanted to eliminate root element of the xml document

      select messagetext from bl_dm_message where class='1223'

      messagetext
      ------------------
      <?xml version="1.0" encoding="WINDOWS-1252"?>
      <CDMUpdates effectiveDateTime="2012-04-11T12:02:23.526" fileId="1255340" batchId="857210" UpdateId="66265582">
      <Instrument>
      <instrumentId>98781</instrumentId>
      <instrumentBasic>
      <UpdateId>SAMSUNG</UpdateId>
      <instrumentId>98781</instrumentId>
      <instrumentDebt>
      <UpdateId>SAMSUNG</UpdateId>
      <instrumentId>98781</instrumentId>
      <revenueSourceCd>XOVR</revenueSourceCd>
      </instrumentDebt>
      </instrumentBasic>
      <redemptionCallTerms>
      <UpdateId>SAMSUNG</UpdateId>
      <instrumentId>98781</instrumentId>
      <callFrequencyCode>A</callFrequencyCode>
      </redemptionCallTerms>
      </Instrument>
      </CDMUpdates>



      Expecting the following result i.e ,wanted to eliminate root element 'CDMUpdates'

      Expecting o/p:


      <Instrument>
      <instrumentId>98781</instrumentId>
      <instrumentBasic>
      <UpdateId>SAMSUNG</UpdateId>
      <instrumentId>98781</instrumentId>
      <instrumentDebt>
      <UpdateId>SAMSUNG</UpdateId>
      <instrumentId>98781</instrumentId>
      <revenueSourceCd>XOVR</revenueSourceCd>
      </instrumentDebt>
      </instrumentBasic>
      <redemptionCallTerms>
      <UpdateId>SAMSUNG</UpdateId>
      <instrumentId>98781</instrumentId>
      <callFrequencyCode>A</callFrequencyCode>
      </redemptionCallTerms>
      </Instrument>



      DB version:
      Oracle9i Enterprise Edition Release 11.0.0.1.0 - 64bit Production

      please help me,appreciable for ealy reply .Thanks
        • 1. Re: how to eliminate root element
          Marco Gralike
          Eh release is 9i or 11(probably 9i)?

          If it works in that version you would need to do something like
          select extract(messagetext,'/CDMUpdates/Instrument') from bl_dm_message where class='1223'
          • 2. Re: how to eliminate root element
            odie_63
            Hi,
            DB version:
            Oracle9i Enterprise Edition Release 11.0.0.1.0 - 64bit Production
            That's one peculiar combination...
            SELECT * FROM v$version;
            On 11g databases, use :
            select xmlquery('/CDMUpdates/Instrument' passing messagetext returning content)
            from bl_dm_message
            where class = '1223';
            and on prior versions :
            select extract(messagetext, '/CDMUpdates/Instrument')
            from bl_dm_message
            where class = '1223';
            • 3. Re: how to eliminate root element
              912973
              HI
              Thank you very much for early reply,aim facing one more issue like the comand as you given when i try to execute from java iam getting following exception
              java.sql.SQLException: ORA-00911: invalid character


              String queryForPLdamessage = "select xmlquery('/MDMUpdates/Instrument' passing messagetext returning content) from pl_da_message where class = '1255340';"

              ResultSet rset = stmt.executeQuery(queryForPLdamessage);

              Error o/p:
              java.sql.SQLException: ORA-00911: invalid character

                   at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
                   at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
                   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
                   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
                   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
                   at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
                   at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
                   at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
                   at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
                   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
                   at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
                   at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:386)

              any clue for the exception /
              • 4. Re: how to eliminate root element
                odie_63
                String queryForPLdamessage = "select xmlquery('/MDMUpdates/Instrument' passing messagetext returning content) from pl_da_message where class = '1255340';"
                Try removing the ending semicolon in the query.
                • 5. Re: how to eliminate root element
                  Marco Gralike
                  Please don't double post your questions...

                  java.sql.SQLException: ORA-00911: invalid character

                  :-(