OraFAQ Forum: JDeveloper, Java & XML » Insert xml with spetial character

Home » Developer & Programmer » JDeveloper, Java & XML » Insert xml with spetial character

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Insert xml with spetial character [message #172630]

Wed, 17 May 2006 07:25

reynard
Messages: 1
Registered: May 2006

Junior Member

If I create a table like this:

create table camac.testMR(
id NUMBER (10,0),
xmlData SYS.XMLTYPE
) NOLOGGING;

I can insert a value like this

INSERT into camac.testMR (id, xmlData)
VALUES (1, sys.XMLType.createXML(
'<Warehouse whNo="100">
<Building>Owned</Building>
</Warehouse>'));

It works fine.
How can I insert a record that contains a permil '&permil;' or '&# 2030;'(without the space in the 2 sample) in the xml data?

INSERT into camac.testMR (id, xmlData)
VALUES (3, sys.XMLType.createXML(
'<Warehouse whNo="100">
<Building>Owned&# 2030;</Building>
</Warehouse>'));

Then I get an error
LPX-00217: invalid character 2030 (\u07EE)

The NLS_NCHAR_CHARACTERSET of the database is AL16UTF16

Thanks

Report message to a moderator

Re: Insert xml with spetial character [message #181633 is a reply to message #172630]

Mon, 10 July 2006 23:28

hobbes
Messages: 173
Registered: January 2006

Senior Member

- Replace the ampersand in your content with the XML entity reference & [Refer this link for more information on handling special content in XML.]
- When inserting through SQL*Plus, disable substitution variable prompting.

SQL> SET ESCAPE "\"
SQL> INSERT into testMR (id, xmlData)
  2  VALUES (1, sys.XMLType.createXML(
  3  '<Warehouse whNo="100"> 
  4  <Building>Owned</Building>
  5  </Warehouse>')); 

1 row created.

SQL> INSERT into testMR (id, xmlData)
  2  VALUES (3, sys.XMLType.createXML(
  3  '<Warehouse whNo="100"> 
  4  <Building>Owned\&amp;# 2030;</Building>
  5  </Warehouse>')); 

1 row created.

SQL> SELECT t.id, t.xmldata.getStringVal()
  2  FROM   testMR t;

        ID
----------
T.XMLDATA.GETSTRINGVAL()
-------------------------------------------
         1
<Warehouse whNo="100">
<Building>Owned</Building>
</Warehouse>

         3
<Warehouse whNo="100">
<Building>Owned&amp;# 2030;</Building>
</Warehouse>

Report message to a moderator

Previous Topic:	XML parsing using PL\SQL
Next Topic:	extractvalue returns null

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 09:36:03 CDT 2024