Oracle XMLtype data type

[Former Member]

Latest Powerbuilder 12.5.2

Oracle 11g

How can I update a XMLType column?

can select it, but errors on update.

[Former Member]

I suspect that you will need to treat it as a clob, on your select you use the .getCLOBVal() syntax to convert the XMLType to a string.

Likewise on your update you use the XMLTYPE function to convert the string back to XML.

Something like this:

UPDATE purchaseorder

  SET OBJECT_VALUE = XMLType(<xmlstring>), nls_charset_id('AL32UTF8')) where ....

Not tried this but I hope it helps.

David

[Former Member]

The syntax is not correct.  I am not sure how to test that.  I tried

   set xml_setting = xmltype(convert(:ls_xmlData , 'UTF8'))

this also had the same error.

[Former Member]

Sorry about that, I got the information from this link:

XMLType Operations

I guess we need to get the right syntax in sqlplus (or equivalent) then map that to PB.

The other alternative is to create a procedure to do the update for you, pass in a clob and convert that. The 4k limit you are seeing is the limit on a varchar2. All the xml work I have done has involved endless conversions.

CREATE OR REPLACE PROCEDURE xmlupdate (xmlin IN CLOB, xmlout OUT CLOB)

IS

   txml           XMLTYPE;

BEGIN

-- Convert Clob Received into an XML document

   txml := xmltype (xmlin);

.....

END;

[Former Member]

I am guessing that Powerbuilder is not binding the variable correctly. I have this working in Java.

[Former Member]

After trying all different ways I cannot get it to work. Looking at the PB documentation the XMLTYPE is handled as a string, and oracle limits you to 4000 characters. If I go over that I get string literal too long.

So I conclude the procedure method is the only way to update large XML strings.

This is the syntax I used, as you can see I tried it with two strings less that 4000 just in case, but once the string went over 4000 I got the error.

update timesheets.test set xmlstuff = xmltype('<XX>'||:xmlss1||:xmlss1||'</XX>') where keyx  = '1

Message was edited by: David Peace

[Former Member]

FWIW: I have this working with ASE and SS - but, I use the TEXT data type and the Slect/Update Blog commands.

[Former Member]

I also have it working with MS SQL Server and SQL ANywhere, but that's not Oracle.

[Former Member]

Does anyone still use Oracle?   LOL