Home » SQL & PL/SQL » SQL & PL/SQL » convert clob into xmltype
convert clob into xmltype [message #118344] Wed, 04 May 2005 09:57 Go to next message
shooter
Messages: 44
Registered: February 2005
Member
I am working on Oracle 9i.

I need to implement a PL/SQL function which gets a CLOB document, converts it into XMLType, and returns it as XMLType.

The file path of the document must be passed as a varchar2.

create or replace function clob_to_xmltype( filestr in varchar2 ) return xmltype
is
begin
  
  ...
  
end;


Is there an in-built function I can use for the conversion?
Re: convert clob into xmltype [message #118351 is a reply to message #118344] Wed, 04 May 2005 10:39 Go to previous message
Frank Naude
Messages: 4580
Registered: April 1998
Senior Member
You can use the XMLType() constructor to construct an XMLType instance. The constructor can take in the XML as a CLOB, VARCHAR2 or take in a object type. However, if you insist, you can try something like this:

SQL> CREATE TABLE clobtab (clobcol CLOB);

Table created.

SQL> INSERT INTO clobtab VALUES (
  2  '<?xml version="1.0"?>
  3   <EMP>
  4     <EMPNO>221</EMPNO>
  5     <ENAME>John</ENAME>
  6   </EMP>');

1 row created.

SQL>
SQL> CREATE OR REPLACE FUNCTION to_xmltype (clobcol CLOB)
  2  RETURN XMLTYPE AS
  3  BEGIN
  4     RETURN XMLType(clobcol);
  5  END;
  6  /

Function created.

SQL> show errors
No errors.
SQL>
SQL> SELECT to_xmltype(clobcol) FROM clobtab;

TO_XMLTYPE(CLOBCOL)
--------------------------------------------------------------------------------
<?xml version="1.0"?>
 <EMP>
   <EMPNO>221</EMPNO>
   <ENAME>John</ENAME>
 </EMP


Best regards.

Frank
Previous Topic: Where is the log for the DDL statement?
Next Topic: Datatype Error in Peoplesoft Union Query-HELP!
Goto Forum:
  


Current Time: Sat May 25 18:47:14 CDT 2024