This discussion is archived
5 Replies Latest reply: Apr 15, 2012 8:20 AM by Marco Gralike RSS

how to eliminate root element

912973 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    Please don't double post your questions...

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

    :-(

Legend

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