Converting from a Varchar in pl/sql which is more than 4000 characters to a XMLTYPE

Question

I came across a problem where I need to convert a string of more than 4000 characters to a XMLTYPE. I tried using

XMLTYPE.CREATEXML("MY STRING")
XMLTYPE("MY STRING")

In both cases, I got 'String literal too long..' error.

Answer 1

We can pass only the data in xml format for creating an XMLTYPE instance and use CLOB to pass the string value and then use the EXTRACTVALUE Function to retreive the data

DECLARE 
str CLOB;
x XMLTYPE;
y CLOB:='<TEXT>MY STRING</TEXT>';
BEGIN
x:= XMLTYPE.CREATEXML(y);
select to_clob(extractvalue(x,'/TEXT')) into str FROM DUAL;
dbms_output.put_line(str);
END;

Please check the links XMLTYPE Examples and XMLTYPE Documantation for more practical examples