-2
select xmltype(clob_content) from table

error

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00007: unexpected end-of-file encountered
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.

Here is my XML:

enter image description here

APC
  • 144,005
  • 19
  • 170
  • 281
PMAS Arid
  • 1
  • 1
  • 2
  • 1
    Most likely the content you have in that column is not a valid XML document. Please [edit] your question to include the XML content you have in that `clob_content` column. – Progman Aug 16 '20 at 08:20
  • Do not add any code as images to your question, add the code to the question itself. – Progman Aug 16 '20 at 09:53

1 Answers1

0

Sample data you posted is OK:

SQL> with test (col) as
  2    (select '
  3      <company>
  4        <employee>
  5          <empno>7839></empno>
  6          <ename>king</ename>
  7          <hiredate>1981-11-17</hiredate>
  8          <sal>5000</sal>
  9          <deptno>10</deptno>
 10          <dname>accounting</dname>
 11        </employee>
 12      </company>'
 13     from dual
 14    )
 15  select xmltype(col) From test;

XMLTYPE(COL)
--------------------------------------------------------
<company>
  <employee>
    <empno>7839&gt;</empno>
    <ename>king</ename>
    <hiredate>1981-11-17</hiredate>
    <sal>5000</sal>
    <deptno>10</deptno>
    <dname>accounting</dname>
  </employee>
</company>


SQL>

but that's probably not the case with the rest of rows in the table. For example:

SQL> with test (col) as
  2    (select '
  3      <company>
  4        <employee>
  5          <empno>7839></empno>
  6          <ename>king<ename>                --> wrong closing tag
  7          <hiredate>1981-11-17</hiredate>
  8          <sal>5000</sal>
  9          <deptno>10</deptno>
 10          <dname>accounting</dname>
 11        </employee>
 12      </company>'
 13     from dual
 14    )
 15  select xmltype(col) From test;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00225: end-element tag "employee" does not match start-element tag "ename"
Error at line 10
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1



no rows selected

SQL>

How to find a culprit? A simple (and probably not very efficient) approach is to loop through the table and apply XMLTYPE to every row, separately. Once you hit the error, display the contents and examine what's wrong with it.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57