Home » Developer & Programmer » JDeveloper, Java & XML » insertXML and special char like ampersand &amp (oracle 9i)
insertXML and special char like ampersand &amp [message #285408] Tue, 04 December 2007 07:26 Go to next message
prigollet
Messages: 2
Registered: December 2007
Location: uk
Junior Member
I'm trying to insert some XML extracted from one DB into another DB.

When string field have special character like ' and & the XML has ' and & but when I tried to load this it gets truncated after the special character.
I've tried editing the XML palying with it and I've tried create a statement prior to the insertXML using setEscapeProcessing but nothing.

Creating statement I don't seem to be able to run something like
set escape \
or
set scan off

so I'm running out of ideas and would appreciate any help.
thanks
Philippe
Re: insertXML and special char like ampersand &amp [message #285608 is a reply to message #285408] Wed, 05 December 2007 02:44 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Could you copy-paste a small example from your SQL*Plus session to show how exactly this is happening? Any usual means of escaping ampersand character (such as "set scan off") should be able to handle special characters in XML.
Re: insertXML and special char like ampersand &amp [message #285653 is a reply to message #285608] Wed, 05 December 2007 04:14 Go to previous messageGo to next message
prigollet
Messages: 2
Registered: December 2007
Location: uk
Junior Member
thanks for the answer.

insertXML (may be I should have specify) is a method from the OracleXMLSave class.

I found one solution which was to replace '&' in my xml by '&'

so a few lines of sed did the trick
sed -e 's/\&/\&/' |\
sed -e 's/\'/\'/' |\
sed -e 's/\>/\>/' |\
sed -e 's/\>/\</' |\

I still think that insertXML should have been able to handle 'bla & bla ' correctly as it is XML compliant and load 'bla & bla' in my DB. At least I have a workaround but if someone has an explanation I'd still be interested.


thanks
Philippe
Re: insertXML and special char like ampersand &amp [message #286881 is a reply to message #285408] Mon, 10 December 2007 06:14 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
OK, from your first post I got the impression that you were talking of DBMS_XMLSave.insertXML and running it from the SQL*Plus session.

I set up a small test for OracleXMLSave.insertXML on Oracle 9.2.0.1 and it did handle special characters correctly. With this code:
import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;
public class testXMLSave
{
   public static void main(String argv[])
     throws SQLException
  {
      Connection conn = getConnection("scott","tiger");
      OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");

      sav.insertXML("<?xml version='1.0'?><ROWSET><ROW num=\"1\">
                    <EMPNO>1</EMPNO>
                    <ENAME>O&apos;Connor</ENAME>
                    <JOB>CLERK</JOB>
                    <MGR>7902</MGR>
                    <HIREDATE>12/17/1980 0:0:0</HIREDATE>
                    <SAL>1800</SAL>
                    <DEPTNO>20</DEPTNO>
                    </ROW></ROWSET>");
      sav.close();
   }

    private static Connection getConnection(String user, String passwd)
      throws SQLException
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection conn =
          DriverManager.getConnection("jdbc:oracle:thin:@db",user,passwd);
     return conn;
   }
}

"O&apos;Connor" was correctly inserted as "O'Connor".

Are you on a different Oracle version or does your code look different?
Previous Topic: ClassNotFoundException: oracle.jdbc.driver.OracleDriver
Next Topic: how can i use been area in semple way
Goto Forum:
  


Current Time: Mon Dec 05 14:59:09 CST 2016

Total time taken to generate the page: 0.13861 seconds