CODEWITHSUNDEEP
xmloracle-database
Betlista
Betlista

Reputation: 10549

ORA-00904: "CREATEXML": invalid identifier

I want to retrieve value from XML data stored as CLOB in Oracle 11g DB.

I tried

create table xml_test(
  id number,
  xml_text clob
);

insert into xml_test 
values(
  1, to_clob('<?xml version="1.0" encoding="UTF-8"?><rule name="name"></rule>')
);

select createXML(xml_text) from xml_test;

(SQL fiddle 1)

(SQL fiddle 2)

What am I doing wrong?

According this documentation this should work with both CLOB and VARCHAR2, right?

My goal is to retrieve @name via XPath

Note: Please, this SQL fiddle example is just an idea, unfortunately it's not working online (NullPointerException), it works fine in my DB (up to createXML functions in queries)

Upvotes: 1

Views: 1372

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12169

CreateXML() is not a global SQL function. It is a static method on the XMLTYPE object. Try this:

select xmltype.createXML(xml_text) from xml_test;

See intro in the doc, "XMLType is a system-defined opaque type for handling XML data. It has predefined member functions on it to extract XML nodes and fragments."

Upvotes: 2

Related Questions