I have a column in DB which is of clob type.
I need to extract the text in one of the tag using a sql query but i am getting a xml parsing error.
xml in the clob col:
<ns2:reference xmlns:ns2="">
<ns2:meta-data>
<content-type>REFERENCE</content-type>
<content-subtype>CONFERENCE</content-subtype>
</ns2:meta-data>
<ns2:reference-core>
<ns2:publication_id>4069</ns2:publication_id>
Query used is :
SELECT EXTRACTVALUE(xmltype(CONTENT_XML), '//ns2:publication_id') FROM table where content_id=313;
Please help me with this...
Thanks in advance
You have to specify a value for the namespace for Oracle to convert the clob to an XMLType:
SQL> create table t (
2 x clob
3 );
Table created.
SQL>
SQL> insert into t values ('<ns2:reference xmlns:ns2="">
2 <ns2:meta-data>
3 <content-type>REFERENCE</content-type>
4 <content-subtype>CONFERENCE</content-subtype>
5 </ns2:meta-data>
6 <ns2:reference-core>
7 <ns2:publication_id>4069</ns2:publication_id>
8 </ns2:reference-core>
9 </ns2:reference>');
1 row created.
SQL>
SQL> SELECT xmltype(x)
2 FROM t;
ERROR:
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00284: namespace prefix to NULL URI is not allowed
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
Once you supply one, you can extract the value as needed:
SQL> delete t;
1 row deleted.
SQL> insert into t values ('<ns2:reference xmlns:ns2="http://www.oracle.com">
2 <ns2:meta-data>
3 <content-type>REFERENCE</content-type>
4 <content-subtype>CONFERENCE</content-subtype>
5 </ns2:meta-data>
6 <ns2:reference-core>
7 <ns2:publication_id>4069</ns2:publication_id>
8 </ns2:reference-core>
9 </ns2:reference>');
1 row created.
SQL>
SQL> SELECT
2 xmltype(x),
3 extractvalue(xmltype(x),
4 '//*/ns2:reference-core/ns2:publication_id',
5 'xmlns:ns2="http://www.oracle.com"') pub_id
6 FROM t;
XMLTYPE(X)
------------------------------------------------------------------------------
PUB_ID
------------------------------------------------------------------------------
<ns2:reference xmlns:ns2="http://www.oracle.com">
<ns2:meta-data>
<content
4069