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
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 #571793 is a reply to message #571792] Fri, 30 November 2012 09:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: LPX-00210: expected '<' instead of '?' [message #571795 is a reply to message #571792] Fri, 30 November 2012 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain us what does the code intend to do.

Regards
Michel
Re: LPX-00210: expected '<' instead of '?' [message #571797 is a reply to message #571795] Fri, 30 November 2012 10:12 Go to previous messageGo to next message
aman_sharma_02
Messages: 4
Registered: November 2012
Location: PUNE
Junior Member
Need to insert the values of xml file in oracle table.
Re: LPX-00210: expected '<' instead of '?' [message #571798 is a reply to message #571797] Fri, 30 November 2012 10:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 #571799 is a reply to message #571798] Fri, 30 November 2012 10:52 Go to previous messageGo to next message
aman_sharma_02
Messages: 4
Registered: November 2012
Location: PUNE
Junior Member
Michel,

Requirement is to store Chinese characters in column. Even i am able insert normal data into XML_TEST using my code.
I get the error whenever i try to insert Chinese characters in NCLOB column.

Re: LPX-00210: expected '<' instead of '?' [message #571802 is a reply to message #571799] Fri, 30 November 2012 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if you can mix character sets in a XML.
I move the topic to XML forum.

Regards
Michel
Re: LPX-00210: expected '<' instead of '?' [message #571803 is a reply to message #571802] Fri, 30 November 2012 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I searched on the web and the constant answer I found is what I thought: the NVARCHAR2 or NCLOB datatypes are supported by no XML function.
Your only solution is to recreate your database in AL32UTF8 character set and use the standard CLOB/VARCHAR2 datatypes.

Regards
Michel
Re: LPX-00210: expected '<' instead of '?' [message #571804 is a reply to message #571803] Fri, 30 November 2012 11:29 Go to previous messageGo to next message
aman_sharma_02
Messages: 4
Registered: November 2012
Location: PUNE
Junior Member
Database in AL16UTF16 character set

SELECT * FROM NLS_DATABASE_PARAMETERS
2 ;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
Re: LPX-00210: expected '<' instead of '?' [message #571806 is a reply to message #571804] Fri, 30 November 2012 11:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #571808 is a reply to message #571804] Fri, 30 November 2012 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Database in AL16UTF16 character set


No it is NLS_NCHAR_CHARACTERSET.

Regards
Michel
Re: LPX-00210: expected '<' instead of '?' [message #571809 is a reply to message #571806] Fri, 30 November 2012 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't succeed to do it in "pure" SQL, did you find a way?
Any way I tried it ends with error:
LPX-00200: could not convert from encoding UTF-8 to WINDOWS-1252


Regards
Michel
Re: LPX-00210: expected '<' instead of '?' [message #571810 is a reply to message #571809] Fri, 30 November 2012 13:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel,

Not sure what you mean by "pure" sql. Could you pst what you have?

SY.
Re: LPX-00210: expected '<' instead of '?' [message #571817 is a reply to message #571810] Fri, 30 November 2012 15:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 messageGo to next message
Solomon Yakobson
Messages: 3269
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 #571839 is a reply to message #571817] Sat, 01 December 2012 07:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Could be Bug 9224400

SY.
Re: LPX-00210: expected '<' instead of '?' [message #571842 is a reply to message #571839] Sat, 01 December 2012 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think it is (exactly) the same thing as in this bug the database is also in Unicode ("could not convert from encoding UTF-8 to UCS2") mine is not (WE8MSWIN1252) and I also tried it in 11.2.0.1 and got the same error (was in 10.2.0.4).
I will upgrade my database to 10.2.0.5 and 11.2.0.3 and restest (but it is my laptop db and so not my priority now but I keep this in mind).
What is your version and database character set?

Regards
Michel
Re: LPX-00210: expected '<' instead of '?' [message #571844 is a reply to message #571842] Sat, 01 December 2012 11:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: LPX-00210: expected '<' instead of '?' [message #571845 is a reply to message #571842] Sat, 01 December 2012 11:26 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oh, and NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252

SY.
Re: LPX-00210: expected '<' instead of '?' [message #571846 is a reply to message #571845] Sat, 01 December 2012 13:14 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, so if I upgrade my database to 11.2.0.3 I should no more get the error.
will test it. Thanks.

Regards
Michel
Previous Topic: Connection Between Java and Oracle
Next Topic: Problems with Customized Oracle Identity Manager
Goto Forum:
  


Current Time: Fri Mar 29 10:53:17 CDT 2024