loading xml containing single quotes

[Thargy]

Hello,

I am trying to load data into an xmltype, using sqlplus.
Since this load is part of a whole sequence of scripts, I can't jump ship half way, and use sql loader.

What I want to be able to do is load an xml schema into an xmltype field, by saying

INSERT INTO MESSAGE_DOCUMENT VALUES (1,
   XMLTYPE.createxml('blah blah blah');

However, I have hit two problems, the blah blah part is >4,000 characters (so it has to be split somehow) and it contains single quotes.

Thus, if I attempt to use replace or translate, it only works on the xml until it encounters the first single quote, and then errors.

Does anyone have any ideas as to how I can overcome this?

Regards

Tharg

Grinding away at things Oracular

 

[taupirho]

Can you use PL/SQL ?

If so, check out the following link

http://www.oracle.com/technology/sample_code/tech/java/codesnippet/xmldb/HowToLoadLargeXML.html

 

[Thargy]

taupirho,

I've finally cracked the problem.

The solution is:-

1 Replace all single quotes in the source with a caret(^).
2 Create a staging table with a clob datatype.
3 Insert into clob in chunks < 4,000 characters, replacing caret with single quote.
4 Update target table by making xml from the clob
5 commit and delete the clob staging table.

Job done.

Thanks for the assist.

Tharg

Grinding away at things Oracular