This forum is currently locked. You can't register or post questions at this time. (read more)

XML parsing error

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

tushar_spatil
Member
Posts: 12
Joined: Thu Aug 08, 2013 10:37 am

XML parsing error

Postby tushar_spatil » Thu Aug 08, 2013 10:46 am

Hi All,
I am new in this forum and have limited knowledge of Pl-Sql as I got some requirement from client to import xml file to oracle databaseSo, I did some google and found this excellent forum. I tried with
1)created directory and given grants(read,write) and placed emp.xml file with required permission.
2)created table structure
3)Thanks Tim for Pl-Sql script and demo for importing xml data to relational table, But I am getting error while parsing the xml .Error is shown below.
my oracle RDBMS version is 11.1.0.7



Code: Select all

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for HPUX: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> DECLARE
  2    l_bfile   BFILE;
  3    l_clob    CLOB;
  4    l_parser  dbms_xmlparser.Parser;
  5    l_doc     dbms_xmldom.DOMDocument;
  6    l_nl      dbms_xmldom.DOMNodeList;
  7    l_n       dbms_xmldom.DOMNode;
  8    l_temp    VARCHAR2(1000);
  9
 10    TYPE tab_type IS TABLE OF emp%ROWTYPE;
 11    t_tab  tab_type := tab_type();
 12  BEGIN
 13
 14    l_bfile := BFileName('XML_DIR', 'sample.xml');
 15    dbms_lob.createtemporary(l_clob, cache=>FALSE);
 16    dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
 17    dbms_lob.loadFromFile(dest_lob => l_clob,
 18                          src_lob  => l_bfile,
 19                          amount   => dbms_lob.getLength(l_bfile));
 20    dbms_lob.close(l_bfile);
 21
 22    -- make sure implicit date conversions are performed correctly
 23    dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');
 24
 25    -- Create a parser.
 26    l_parser := dbms_xmlparser.newParser;
 27
 28    -- Parse the document and create a new DOM document.
 29    dbms_xmlparser.parseClob(l_parser, l_clob);
 30    l_doc := dbms_xmlparser.getDocument(l_parser);
 31
 32    -- Free resources associated with the CLOB and Parser now they are no longer needed.
 33    dbms_lob.freetemporary(l_clob);
 34    dbms_xmlparser.freeParser(l_parser);
 35
 36    -- Get a list of all the EMP nodes in the document using the XPATH syntax.
 37    l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMPLOYEE');
 38
 39    -- Loop through the list and create a new record in a tble collection
 40    -- for each EMP record.
 41    FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
 42      l_n := dbms_xmldom.item(l_nl, cur_emp);
 43
 44      t_tab.extend;
 45
 46      -- Use XPATH syntax to assign values to he elements of the collection.
 47      dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
 48      dbms_xslprocessor.valueOf(l_n,'ENAME/text()',t_tab(t_tab.last).ename);
 49      dbms_xslprocessor.valueOf(l_n,'JOB/text()',t_tab(t_tab.last).job);
 50      dbms_xslprocessor.valueOf(l_n,'MGR/text()',t_tab(t_tab.last).mgr);
 51      dbms_xslprocessor.valueOf(l_n,'HIREDATE/text()',t_tab(t_tab.last).hiredate);
 52      dbms_xslprocessor.valueOf(l_n,'SAL/text()',t_tab(t_tab.last).sal);
 53      dbms_xslprocessor.valueOf(l_n,'COMM/text()',t_tab(t_tab.last).comm);
 54      dbms_xslprocessor.valueOf(l_n,'DEPTNO/text()',t_tab(t_tab.last).deptno);
 55    END LOOP;
 56
 57    -- Insert data into the real EMP table from the table collection.
 58    FORALL i IN t_tab.first .. t_tab.last
 59      INSERT INTO emp VALUES t_tab(i);
 60
 61    COMMIT;
 62
 63    -- Free any resources associated with the document now it
 64    -- is no longer needed.
 65    dbms_xmldom.freeDocument(l_doc);
 66
 67  EXCEPTION
 68    WHEN OTHERS THEN
 69      dbms_lob.freetemporary(l_clob);
 70     dbms_xmlparser.freeParser(l_parser);
 71      dbms_xmldom.freeDocument(l_doc);
 72      RAISE;
 73  END;
 74  /
DECLARE
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of 'x'
Error at line 1
ORA-06512: at line 72


thanks
Tp

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XML parsing error

Postby Tim... » Thu Aug 08, 2013 11:17 am

Hi.

That type of error is typical of trying to parse an invalid XML document. See the mention of expecting '<' (the start of a tag), but getting something else.

Code: Select all

ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of 'x'
Error at line 1
ORA-06512: at line 72


So the question is, why is the XML invalid? This can be for a number of reasons.

1) You have downloaded the XML for the website by clicking on it, so it opens in the browser, then copying it manually. Some browsers don't display the raw XML, so when you copy and paste in this way you actually end up with an invalid XML document. Instead, right-click on the link and do a save-as.

2) You didn't really load the XML document into the CLOB. After doing the load, print out the contents of the CLOB variable using DBMS_OUTPUT to check it actually loaded successfully. So this.

Code: Select all

dbms_lob.close(l_bfile);


Becomes this.

Code: Select all

dbms_lob.close(l_bfile);
dbms_output.put_line(l_clob);


Before running your code in SQL*Plus, issue the following command.

Code: Select all

SET SERVERPOUTPUT ON


The contents of the CLOB will now display in your SQL*Plus session. Check it's contents are correct.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

tushar_spatil
Member
Posts: 12
Joined: Thu Aug 08, 2013 10:37 am

Re: XML parsing error

Postby tushar_spatil » Thu Aug 08, 2013 11:37 am

Thanks for quick reply,
I did same what you suggested above right-click and save as link .It is looking like

[*]mia35132orc001:/opt/opapps/opappsval/xmltemp/xml_dir#more emp.xml
<?xml version="1.0"?>
<EMPLOYEES>
<EMP><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><HIREDATE>17-DEC-80</HIREDATE><SAL>800</SAL></EMP>
<EMP><EMPNO>7499</EMPNO><ENAME>ALLEN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>20-FEB-81</HIREDATE><SAL>1600</SAL><COMM>300</COMM></EMP>
<EMP><EMPNO>7521</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>22-FEB-81</HIREDATE><SAL>1250</SAL><COMM>500</COMM></EMP>
<EMP><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-81</HIREDATE><SAL>2975</SAL></EMP>
<EMP><EMPNO>7654</EMPNO><ENAME>MARTIN</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>28-SEP-81</HIREDATE><SAL>1250</SAL><COMM>1400</COMM></EMP>
<EMP><EMPNO>7698</EMPNO><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>01-MAY-81</HIREDATE><SAL>2850</SAL></EMP>
<EMP><EMPNO>7782</EMPNO><ENAME>CLARK</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>09-JUN-81</HIREDATE><SAL>2450</SAL></EMP>
<EMP><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-87</HIREDATE><SAL>3000</SAL></EMP>
<EMP><EMPNO>7839</EMPNO><ENAME>KING</ENAME><JOB>PRESIDENT</JOB><HIREDATE>17-NOV-81</HIREDATE><SAL>5000</SAL></EMP>
<EMP><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</JOB><MGR>7698</MGR><HIREDATE>08-SEP-81</HIREDATE><SAL>1500</SAL><COMM>0</COMM></EMP>
<EMP><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-87</HIREDATE><SAL>1100</SAL></EMP>
<EMP><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</JOB><MGR>7698</MGR><HIREDATE>03-DEC-81</HIREDATE><SAL>950</SAL></EMP>
<EMP><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-81</HIREDATE><SAL>3000</SAL></EMP>
<EMP><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><MGR>7782</MGR><HIREDATE>23-JAN-82</HIREDATE><SAL>1300</SAL></EMP>
</EMPLOYEES>
[*]

then execute script

Code: Select all

SQL> DECLARE
  2    l_bfile   BFILE;
  3    l_clob    CLOB;
  4    l_parser  dbms_xmlparser.Parser;
  5    l_doc     dbms_xmldom.DOMDocument;
  6    l_nl      dbms_xmldom.DOMNodeList;
  7    l_n       dbms_xmldom.DOMNode;
  8    l_temp    VARCHAR2(1000);
  9
 10    TYPE tab_type IS TABLE OF emp%ROWTYPE;
 11    t_tab  tab_type := tab_type();
 12  BEGIN
 13
 14    l_bfile := BFileName('XML_DIR', 'emp.xml');
 15    dbms_lob.createtemporary(l_clob, cache=>FALSE);
 16    dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
 17    dbms_lob.loadFromFile(dest_lob => l_clob,
 18                          src_lob  => l_bfile,
 19                          amount   => dbms_lob.getLength(l_bfile));
 20    dbms_lob.close(l_bfile);
 21    dbms_output.put_line(l_clob);
 22
 23    -- make sure implicit date conversions are performed correctly
 24    dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');
 25
 26    -- Create a parser.
 27    l_parser := dbms_xmlparser.newParser;
 28
 29    -- Parse the document and create a new DOM document.
 30    dbms_xmlparser.parseClob(l_parser, l_clob);
 31    l_doc := dbms_xmlparser.getDocument(l_parser);
 32
 33    -- Free resources associated with the CLOB and Parser now they are no longer needed.
 34    dbms_lob.freetemporary(l_clob);
 35    dbms_xmlparser.freeParser(l_parser);
 36
 37    -- Get a list of all the EMP nodes in the document using the XPATH syntax.
 38    l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');
 39
 40    -- Loop through the list and create a new record in a tble collection
 41    -- for each EMP record.
 42    FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
 43      l_n := dbms_xmldom.item(l_nl, cur_emp);
 44
 45      t_tab.extend;
 46
 47      -- Use XPATH syntax to assign values to he elements of the collection.
 48      dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
 49      dbms_xslprocessor.valueOf(l_n,'ENAME/text()',t_tab(t_tab.last).ename);
 50      dbms_xslprocessor.valueOf(l_n,'JOB/text()',t_tab(t_tab.last).job);
 51      dbms_xslprocessor.valueOf(l_n,'MGR/text()',t_tab(t_tab.last).mgr);
 52      dbms_xslprocessor.valueOf(l_n,'HIREDATE/text()',t_tab(t_tab.last).hiredate);
 53      dbms_xslprocessor.valueOf(l_n,'SAL/text()',t_tab(t_tab.last).sal);
 54      dbms_xslprocessor.valueOf(l_n,'COMM/text()',t_tab(t_tab.last).comm);
 55      dbms_xslprocessor.valueOf(l_n,'DEPTNO/text()',t_tab(t_tab.last).deptno);
 56    END LOOP;
 57
 58    -- Insert data into the real EMP table from the table collection.
 59    FORALL i IN t_tab.first .. t_tab.last
 60      INSERT INTO emp VALUES t_tab(i);
 61
 62    COMMIT;
 63
 64    -- Free any resources associated with the document now it
 65    -- is no longer needed.
 66    dbms_xmldom.freeDocument(l_doc);
 67
 68  EXCEPTION
 69    WHEN OTHERS THEN
 70      dbms_lob.freetemporary(l_clob);
 71     dbms_xmlparser.freeParser(l_parser);
 72      dbms_xmldom.freeDocument(l_doc);
 73      RAISE;
 74  END;
 75  /
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????
DECLARE
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '?'
Error at line 1
ORA-06512: at line 73


What am i doing wrong here?

Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XML parsing error

Postby Tim... » Thu Aug 08, 2013 11:46 am

Hi.

I don't know what these lines are:

Code: Select all

[*]mia35132orc001:/opt/opapps/opappsval/xmltemp/xml_dir#more emp.xml

[*]


They are certainly not valid XML, so if they are in the file you have saved they will need to be removed.

It would appear, the file you are reading contains just the '?' character many times, which is obviously not valid XML. :)

I wonder if this may be a character set conversion issue?

What character set is your database using and what character set is your client PC using?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

tushar_spatil
Member
Posts: 12
Joined: Thu Aug 08, 2013 10:37 am

Re: XML parsing error

Postby tushar_spatil » Thu Aug 08, 2013 11:53 am

Hi Tim,
I corrected xml file and restore new one but same error persists.
RDBMS=11.1.0.7
NLS_NCHAR_CHARACTERSET = AL16UTF16
NLS_CHARACTERSET = UTF8

[*]<?xml version="1.0"?>
<EMPLOYEES>
<EMP><EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR><HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL></EMP>
<EMP>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR><HIREDATE>20-FEB-81</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
</EMP>
<EMP>
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR><HIREDATE>22-FEB-81</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
</EMP>
<EMP>
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR><HIREDATE>02-APR-81</HIREDATE>
<SAL>2975</SAL>
</EMP>
<EMP>
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR><HIREDATE>28-SEP-81</HIREDATE>
<SAL>1250</SAL>
<COMM>1400</COMM>
</EMP>
<EMP>
<EMPNO>7698</EMPNO>
<ENAME>BLAKE</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR><HIREDATE>01-MAY-81</HIREDATE>
<SAL>2850</SAL>
</EMP>
<EMP>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-JUN-81</HIREDATE>
<SAL>2450</SAL>
</EMP>
<EMP>
<EMPNO>7788</EMPNO>
<ENAME>SCOTT</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>19-APR-87</HIREDATE>
<SAL>3000</SAL>
</EMP>
<EMP>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-NOV-81</HIREDATE>
<SAL>5000</SAL>
</EMP>
<EMP>
</EMPLOYEES>
[*]

Code: Select all

????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
????????????????????????????????????????????????????????????????????????????????
???????????????????????
DECLARE
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '?'
Error at line 1
ORA-06512: at line 73


Thanks

tushar_spatil
Member
Posts: 12
Joined: Thu Aug 08, 2013 10:37 am

Re: XML parsing error

Postby tushar_spatil » Thu Aug 08, 2013 1:23 pm

Hi,

Any clue for this error?

thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XML parsing error

Postby Tim... » Thu Aug 08, 2013 4:41 pm

Hi.

What is the [*] at the start and end of the file? This is not valid XML.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

tushar_spatil
Member
Posts: 12
Joined: Thu Aug 08, 2013 10:37 am

Re: XML parsing error

Postby tushar_spatil » Fri Aug 09, 2013 8:42 am

Hi Tim,

" [*] " that is just a tag. I saved eml.xml file SAVE AS and then opened with microsoft word using UTF-8 character and then transfer to my unix server.

emp.xml file as below

<?xml version="1.0"?>
<EMPLOYEES>
<EMP><EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR><HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL></EMP>
<EMP>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR><HIREDATE>20-FEB-81</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
</EMP>
<EMP>
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR><HIREDATE>22-FEB-81</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
</EMP>
<EMP>
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR><HIREDATE>02-APR-81</HIREDATE>
<SAL>2975</SAL>
</EMP>
<EMP>
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR><HIREDATE>28-SEP-81</HIREDATE>
<SAL>1250</SAL>
<COMM>1400</COMM>
</EMP>
<EMP>
<EMPNO>7698</EMPNO>
<ENAME>BLAKE</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR><HIREDATE>01-MAY-81</HIREDATE>
<SAL>2850</SAL>
</EMP>
<EMP>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-JUN-81</HIREDATE>
<SAL>2450</SAL>
</EMP>
<EMP>
<EMPNO>7788</EMPNO>
<ENAME>SCOTT</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>19-APR-87</HIREDATE>
<SAL>3000</SAL>
</EMP>
<EMP>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-NOV-81</HIREDATE>
<SAL>5000</SAL>
</EMP>
<EMP>
</EMPLOYEES>

Am i hitting some bug in oracle RDBMS 11.1.0.7?
But I am not confident wether i am doing it all right Please guide me to resolve it.

Thanks and Regds
Tp

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XML parsing error

Postby Tim... » Fri Aug 09, 2013 12:06 pm

Hi.

I did the following, exactly as described in this article.

http://www.oracle-base.com/articles/9i/ ... nts-9i.php

It worked fine for me in 11.2 and 12.1.

Code: Select all

-- Store emp.xml in the "/tmp" directory on the DB server.

CONN / AS SYSDBA

CREATE DIRECTORY xml_dir AS '/tmp/';
GRANT READ ON DIRECTORY xml_dir TO test;

CONN test/test

DECLARE
  l_bfile   BFILE;
  l_clob    CLOB;
  l_parser  dbms_xmlparser.Parser;
  l_doc     dbms_xmldom.DOMDocument;
  l_nl      dbms_xmldom.DOMNodeList;
  l_n       dbms_xmldom.DOMNode;
  l_temp    VARCHAR2(1000);

  TYPE tab_type IS TABLE OF emp%ROWTYPE;
  t_tab  tab_type := tab_type();
BEGIN

  l_bfile := BFileName('XML_DIR', 'emp.xml');
  dbms_lob.createtemporary(l_clob, cache=>FALSE);
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  dbms_lob.loadFromFile(dest_lob => l_clob,
                        src_lob  => l_bfile,
                        amount   => dbms_lob.getLength(l_bfile));
  dbms_lob.close(l_bfile);
 
  -- make sure implicit date conversions are performed correctly
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');

  -- Create a parser.
  l_parser := dbms_xmlparser.newParser;

  -- Parse the document and create a new DOM document.
  dbms_xmlparser.parseClob(l_parser, l_clob);
  l_doc := dbms_xmlparser.getDocument(l_parser);

  -- Free resources associated with the CLOB and Parser now they are no longer needed.
  dbms_lob.freetemporary(l_clob);
  dbms_xmlparser.freeParser(l_parser);

  -- Get a list of all the EMP nodes in the document using the XPATH syntax.
  l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/EMPLOYEES/EMP');

  -- Loop through the list and create a new record in a tble collection
  -- for each EMP record.
  FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
    l_n := dbms_xmldom.item(l_nl, cur_emp);

    t_tab.extend;

    -- Use XPATH syntax to assign values to he elements of the collection.
    dbms_xslprocessor.valueOf(l_n,'EMPNO/text()',t_tab(t_tab.last).empno);
    dbms_xslprocessor.valueOf(l_n,'ENAME/text()',t_tab(t_tab.last).ename);
    dbms_xslprocessor.valueOf(l_n,'JOB/text()',t_tab(t_tab.last).job);
    dbms_xslprocessor.valueOf(l_n,'MGR/text()',t_tab(t_tab.last).mgr);
    dbms_xslprocessor.valueOf(l_n,'HIREDATE/text()',t_tab(t_tab.last).hiredate);
    dbms_xslprocessor.valueOf(l_n,'SAL/text()',t_tab(t_tab.last).sal);
    dbms_xslprocessor.valueOf(l_n,'COMM/text()',t_tab(t_tab.last).comm);
    dbms_xslprocessor.valueOf(l_n,'DEPTNO/text()',t_tab(t_tab.last).deptno);
  END LOOP;

  -- Insert data into the real EMP table from the table collection.
  FORALL i IN t_tab.first .. t_tab.last
    INSERT INTO emp VALUES t_tab(i);

  COMMIT;

  -- Free any resources associated with the document now it
  -- is no longer needed.
  dbms_xmldom.freeDocument(l_doc);

EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.freetemporary(l_clob);
    dbms_xmlparser.freeParser(l_parser);
    dbms_xmldom.freeDocument(l_doc);
END;
/


I don't have an 11.1 instance here to test against, but I do not believe this has changed since 9i.

So the only things I can think of are:

1) Your XML file is corrupt.
2) Oracle does not have permission to read the file properly.
3) You have some character set conversion issues. You have to make sure the character set listed in your NLS_LANG environment variable matches that of your database.

If it is not one of those three issues, then I really don't know what it can be.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

tushar_spatil
Member
Posts: 12
Joined: Thu Aug 08, 2013 10:37 am

Re: XML parsing error

Postby tushar_spatil » Fri Aug 09, 2013 12:20 pm

Hi Tim,

I have raised this issue with oarcle support and they cameup with saying
" Maybe the parsing error is due to a wrong character in your XML document which does not match the existing DB character set
or you are facing a known defect, ie 12621554, unfortunately no patch is available for 11.1.0.7 HP-UX Itanium platform.
We strongly recommend to upgrade to 11.2.0.3 patchset, Patch 10404530"
I Will update soon once it get confirmed.

Thanks
Tp

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XML parsing error

Postby Tim... » Fri Aug 09, 2013 12:23 pm

OK. Good luck.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

tushar_spatil
Member
Posts: 12
Joined: Thu Aug 08, 2013 10:37 am

Re: XML parsing error

Postby tushar_spatil » Fri Aug 09, 2013 12:46 pm

Ok.. Thanks for your support Tim

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XML parsing error

Postby Tim... » Fri Aug 09, 2013 12:53 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

tushar_spatil
Member
Posts: 12
Joined: Thu Aug 08, 2013 10:37 am

Re: XML parsing error

Postby tushar_spatil » Mon Aug 26, 2013 8:11 am

Hi Tim,

Its quite long back here... SO there is a bug in oracle 11gR1 while importing xml file data to oracle database. I installed 11.2.0.3 and Now I am able to import the xml to oracle database.

Thanks and Rgds
Tushar

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: XML parsing error

Postby Tim... » Mon Aug 26, 2013 8:23 am

Cool. Thanks for the feedback. It helps if someone else stumbles on this thread. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 1 guest

cron