Home » Developer & Programmer » JDeveloper, Java & XML » LPX-00210: expected '<' instead of '?' (Oracle -10.2.0.5.0 || SunOS 5.10 Generic_144488-11 sun4u sparc SUNW,Sun-Fire)
LPX-00210: expected '<' instead of '?' [message #571792] |
Fri, 30 November 2012 09:22 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/3177d8e9f16bbf92488c03fd5ca3389c?s=64&d=mm&r=g) |
aman_sharma_02
Messages: 4 Registered: November 2012 Location: PUNE
|
Junior Member |
|
|
Getting below ORA error.Any help will be appreciated
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML
processing
LPX-00210: expected '<' instead of '?'
Error at line 1
PL/SQL procedure successfully completed.
SQL>SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
------------------------------ -------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
Table:-
CREATE TABLE XML_TEST
(
CONTESTNUMBER VARCHAR2(20 BYTE),
JOBTYPE VARCHAR2(20 BYTE),
JOBFAMILY VARCHAR2(20 BYTE),
INTERNALDESCRIPTION_EN NCLOB
);
xml file :-
<?xml version="1.0" encoding="AL16UTF16"?>
<record>
<CONTESTNUMBER>12000001IX</CONTESTNUMBER>
<JOBTYPE>Experienced</JOBTYPE>
<JOBFAMILY>HR</JOBFAMILY>
<INTERNALDESCRIPTION_EN>症例報告書の設計</INTERNALDESCRIPTION_EN>
</record>
PL/SQL Block :-
DECLARE
l_bfile BFILE;
l_clob NCLOB;
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 XML_TEST%ROWTYPE;
t_tab tab_type := tab_type();
BEGIN
DBMS_OUTPUT.PUT_LINE('1');
l_bfile := BFileName('EXP', 'TALEOOUT.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''');
DBMS_OUTPUT.PUT_LINE('2');
-- Create a parser.
l_parser := dbms_xmlparser.newParser;
DBMS_OUTPUT.PUT_LINE('3');
-- Parse the document and create a new DOM document.
dbms_xmlparser.parseClob(l_parser, l_clob);
DBMS_OUTPUT.PUT_LINE('4');
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_OUTPUT.PUT_LINE('5');
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),'/record');
DBMS_OUTPUT.PUT_LINE('6');
-- 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,'CONTESTNUMBER',t_tab(t_tab.last).CONTESTNUMBER);
dbms_xslprocessor.valueOf(l_n,'JOBTYPE',t_tab(t_tab.last).JOBTYPE);
dbms_xslprocessor.valueOf(l_n,'JOBFAMILY',t_tab(t_tab.last).JOBFAMILY);
dbms_xslprocessor.valueOf(l_n,'INTERNALDESCRIPTION_EN',t_tab(t_tab.last).INTERNALDESCRIPTION_EN);
END LOOP;
-- Insert data into the real EMP table from the table collection.
FORALL i IN t_tab.first .. t_tab.last
INSERT INTO XML_TEST 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_OUTPUT.PUT_LINE(SQLERRM);
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeParser(l_parser);
dbms_xmldom.freeDocument(l_doc);
END;
*BlackSwan added {code} tags; please do so yourself in the future
[Updated on: Fri, 30 November 2012 09:30] Report message to a moderator
|
|
|
|
|
|
Re: LPX-00210: expected '<' instead of '?' [message #571798 is a reply to message #571797] |
Fri, 30 November 2012 10:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> insert into XML_TEST (CONTESTNUMBER, JOBTYPE, JOBFAMILY, INTERNALDESCRIPTION_EN)
2 select extractvalue(x.column_value, '//CONTEXTNUMBER') CONTEXTNUMBER,
3 extractvalue(x.column_value, '//JOBTYPE') JOBTYPE,
4 extractvalue(x.column_value, '//JOBFAMILY') JOBFAMILY,
5 extractvalue(x.column_value, '//INTERNALDESCRIPTION_EN') INTERNALDESCRIPTION_EN
6 from table(
7 xmlsequence(
8 extract(
9 xmltype(bfilename('MY_DIR','file.txt'), NLS_CHARSET_ID ('AL32UTF8'))
10 , '//record'))) x
11 /
1 row created.
SQL> select * from XML_TEST ;
CONTESTNUMBER JOBTYPE JOBFAMILY INTERNALDESCRIPTION_EN
-------------------- -------------------- -------------------- ------------------------------
Experienced HR XXXXX
1 row selected.
I changed the value in INTERNALDESCRIPTION_EN because my database does not support chinese characters but it works in the same way (I assumed your file is registered in UTF-8, if it is not the case then change the character set in line 9).
Regards
Michel
[Updated on: Fri, 30 November 2012 10:46] Report message to a moderator
|
|
|
|
|
|
|
Re: LPX-00210: expected '<' instead of '?' [message #571806 is a reply to message #571804] |
Fri, 30 November 2012 11:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Has nothing to do with it. Make sure file TALEOOUT.xml is saved as unicode big endian:
DECLARE
v_clob CLOB;
v_src_offset NUMBER;
v_dest_offset NUMBER;
v_warning NUMBER;
v_src_bfile BFILE;
v_lang_context number;
v_amount NUMBER;
BEGIN
v_src_offset := 1;
v_dest_offset := 1;
v_src_bfile := BFILENAME('TEMP','TALEOOUT.xml');
v_amount := DBMS_LOB.GETLENGTH(v_src_bfile);
v_lang_context := DBMS_LOB.DEFAULT_LANG_CTX;
DBMS_LOB.CREATETEMPORARY(v_clob,FALSE);
DBMS_LOB.OPEN(v_src_bfile,DBMS_LOB.LOB_READONLY);
DBMS_LOB.LOADCLOBFROMFILE(
dest_lob => v_clob,
src_bfile => v_src_bfile,
amount => v_amount,
dest_offset => v_dest_offset,
src_offset => v_src_offset,
bfile_csid => NLS_CHARSET_ID('AL16UTF16'),
lang_context => v_lang_context,
warning => v_warning
);
INSERT
INTO XML_TEST
SELECT CONTESTNUMBER,
JOBTYPE,
JOBFAMILY,
INTERNALDESCRIPTION_EN
from XMLTABLE(
'/record'
PASSING XMLTYPE(v_clob)
COLUMNS
CONTESTNUMBER VARCHAR2(20 byte) PATH 'CONTESTNUMBER',
JOBTYPE VARCHAR2(20 byte) PATH 'JOBTYPE',
JOBFAMILY VARCHAR2(20 byte) PATH 'JOBFAMILY',
INTERNALDESCRIPTION_EN NCLOB PATH 'INTERNALDESCRIPTION_EN'
);
DBMS_LOB.CLOSE(v_src_bfile);
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT *
2 FROM XML_TEST
3 /
CONTESTNUMBER JOBTYPE JOBFAMILY
-------------------- -------------------- --------------------
INTERNALDESCRIPTION_EN
--------------------------------------------------------------------------------
12000001IX Experienced HR
¿¿¿¿¿¿¿¿
SQL>
SY.
|
|
|
|
|
|
Re: LPX-00210: expected '<' instead of '?' [message #571817 is a reply to message #571810] |
Fri, 30 November 2012 15:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I meant without PL/SQL:
SQL> INSERT INTO XML_TEST
2 SELECT CONTESTNUMBER, JOBTYPE, JOBFAMILY, INTERNALDESCRIPTION_EN
3 from XMLTABLE ('/record'
4 PASSING XMLTYPE(bfilename('MY_DIR','file2.txt'), NLS_CHARSET_ID ('AL32UTF8'))
5 COLUMNS
6 CONTESTNUMBER VARCHAR2(20 byte) PATH 'CONTESTNUMBER',
7 JOBTYPE VARCHAR2(20 byte) PATH 'JOBTYPE',
8 JOBFAMILY VARCHAR2(20 byte) PATH 'JOBFAMILY',
9 INTERNALDESCRIPTION_EN NCLOB PATH 'INTERNALDESCRIPTION_EN'
10 )
11 /
PASSING XMLTYPE(bfilename('MY_DIR','file2.txt'), NLS_CHARSET_ID ('AL32UTF8'))
*
ERROR at line 4:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00200: could not convert from encoding UTF-8 to WINDOWS-1252
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 295
ORA-06512: at line 1
SQL> INSERT INTO XML_TEST
2 SELECT CONTESTNUMBER, JOBTYPE, JOBFAMILY, INTERNALDESCRIPTION_EN
3 from (select bfilename('MY_DIR','file2.txt') v from dual) x,
4 XMLTABLE ('/record'
5 PASSING XMLTYPE(x.v, NLS_CHARSET_ID ('AL32UTF8'))
6 COLUMNS
7 CONTESTNUMBER VARCHAR2(20 byte) PATH 'CONTESTNUMBER',
8 JOBTYPE VARCHAR2(20 byte) PATH 'JOBTYPE',
9 JOBFAMILY VARCHAR2(20 byte) PATH 'JOBFAMILY',
10 INTERNALDESCRIPTION_EN NCLOB PATH 'INTERNALDESCRIPTION_EN'
11 )
12 /
PASSING XMLTYPE(x.v, NLS_CHARSET_ID ('AL32UTF8'))
*
ERROR at line 5:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00200: could not convert from encoding UTF-8 to WINDOWS-1252
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 295
ORA-06512: at line 1
Regards
Michel
[Updated on: Fri, 30 November 2012 15:04] Report message to a moderator
|
|
|
Re: LPX-00210: expected '<' instead of '?' [message #571834 is a reply to message #571817] |
Sat, 01 December 2012 05:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel,
It works fine on my Windows 7:
SQL> SELECT CONTESTNUMBER, JOBTYPE, JOBFAMILY, INTERNALDESCRIPTION_EN
2 from XMLTABLE ('/record'
3 PASSING XMLTYPE(bfilename('TEMP','TALEOOUT.xml'), NLS_CHARSET_ID ('AL16UTF16'
))
4 COLUMNS
5 CONTESTNUMBER VARCHAR2(20 byte) PATH 'CONTESTNUMBER',
6 JOBTYPE VARCHAR2(20 byte) PATH 'JOBTYPE',
7 JOBFAMILY VARCHAR2(20 byte) PATH 'JOBFAMILY',
8 INTERNALDESCRIPTION_EN NCLOB PATH 'INTERNALDESCRIPTION_EN'
9 )
10 /
CONTESTNUMBER JOBTYPE JOBFAMILY
-------------------- -------------------- --------------------
INTERNALDESCRIPTION_EN
--------------------------------------------------------------------------------
12000001IX Experienced HR
¿¿¿¿¿¿¿¿
SQL>
SY.
|
|
|
|
|
Re: LPX-00210: expected '<' instead of '?' [message #571844 is a reply to message #571842] |
Sat, 01 December 2012 11:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
SQL> SELECT *
2 FROM v$version
3 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> SELECT *
2 FROM nls_database_parameters
3 /
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.3.0
20 rows selected.
SQL> SELECT *
2 FROM nls_session_parameters
3 /
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
SQL> SELECT CONTESTNUMBER, JOBTYPE, JOBFAMILY, INTERNALDESCRIPTION_EN
2 from XMLTABLE ('/record'
3 PASSING XMLTYPE(bfilename('TEMP','TALEOOUT.xml'), NLS_CHARSET_ID ('AL16UTF16'
))
4 COLUMNS
5 CONTESTNUMBER VARCHAR2(20 byte) PATH 'CONTESTNUMBER',
6 JOBTYPE VARCHAR2(20 byte) PATH 'JOBTYPE',
7 JOBFAMILY VARCHAR2(20 byte) PATH 'JOBFAMILY',
8 INTERNALDESCRIPTION_EN NCLOB PATH 'INTERNALDESCRIPTION_EN'
9 )
10 /
CONTESTNUMBER JOBTYPE JOBFAMILY
-------------------- -------------------- --------------------
INTERNALDESCRIPTION_EN
--------------------------------------------------------------------------------
12000001IX Experienced HR
¿¿¿¿¿¿¿¿
SQL>
SY.
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:46:18 CDT 2024
|