CODEWITHSUNDEEP
xmloracle-databasejdbcoracle-sqldeveloper
user3793935
user3793935

Reputation: 489

Query XMLELEMENT result is oracle/xdb/XMLType

I have to create a XML from a table (and insert XML stuff into this table, but I'll do it step by step ...) and after some googling I found this: http://allthingsoracle.com/generating-xml-from-sql-and-pl-sql-part-1/

So I tried the first part:

SELECT XMLElement( "NAME"
                 , NAME
                 )   FROM EMP;

But instead of getting something like this:

<NAME>bla</NAME>      
<NAME>muh</NAME>

I only get:

oracle/xdb/XMLType

as a result. Is there anything I have to do before I can use the XMLElement?(I'm new to PL/SQL and this is pretty confusing)

I am using the SQL-Developer with the Version 4.1.2.20 on a Ubuntu 14.04 (I guess) machine.

Upvotes: 1

Views: 3795

Answers (1)

user330315
user330315

Reputation:

The "plain" Oracle JDBC driver (ojdbc.jar) does not support XML (neither the standard JDBC XML API, nor Oracle's internal XML API).

To enable the XML support you need to include the jar files xdb6.jar and xmlparserv2.jar.

xdb6.jar can be downloaded from the same page as the Oracle JDBC driver. xmlparserv2.jar seems to be only available as part of an Oracle server installation but I might be wrong there.

Edit:

It seems in SQL Developer all you need to do is to enable the display of XML:

enter image description here

Another option is to simply return the XML as a CLOB to the client:

SELECT XMLElement("NAME", NAME).getClobVal()
FROM emp;

That doesn't need any additional libraries

Upvotes: 3

Related Questions