convert clob into xmltype [message #118344] |
Wed, 04 May 2005 09:57 |
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 |
Frank Naude
Messages: 4587 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
|
|
|