CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11gxml-parsing
Root
Root

Reputation: 147

How to validate XML file in Oracle PLSQL

My XML file looks below format 

 <?xml version = '1.0'?>
<ROWSET>
   <ROW>
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
   </ROW>
   <ROW>
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
   <ROW>
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW  -- Now corresponding closing tag is missed in this line
</ROWSET>

Now My corresponding closing tag was missed in any of the nodes. In PLSQL programming how can identify the missing tag? XMLDB option I can use,But I would like do it in PLSQL.

Upvotes: 1

Views: 3195

Answers (1)

Aleksej
Aleksej

Reputation: 22949

If you only need to check if it is a valid XML, you can simply try converting it, handlng the error; for example:

good xml:

SQL> select xmltype(q'[<?xml version = '1.0'?>
  2                  <ROWSET>
  3                     <ROW>
  4                        <EMPNO>7369</EMPNO>
  5                     </ROW>
  6                  </ROWSET>]')
  7  from dual;

XMLTYPE(Q'[<?XMLVERSION='1.0'?><ROWSET><ROW><EMPNO>7369</EMPNO></ROW></ROWSET>]'
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <EMPNO>7369</EMPNO>
  </ROW>
</ROWSET

bad xml:

SQL> select xmltype(q'[<?xml version = '1.0'?>
  2                  <ROWSET>
  3                     <ROW>
  4                        <EMPNO>7369</EMPNO>
  5                     </ROW
  6                  </ROWSET>]')
  7  from dual;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: invalid character 60 ('<') found in a Name or Nmtoken
Error at line 6
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1

Upvotes: 4

Related Questions