Home » SQL & PL/SQL » SQL & PL/SQL » ora 31011 xml parsing failed lpx 00216
ora 31011 xml parsing failed lpx 00216 [message #470211] Mon, 09 August 2010 05:57 Go to next message
abcraj
Messages: 5
Registered: August 2010
Location: BAHRAIN
Junior Member
hi,

please see the below steps and let me know what went wrong on my sql . appreciate your support


STEP1: LOAD THE XML FILE INTO A TABLE UM_TEST_X, THE FILE CONTENT IS ATTACHED I THIS DOCUMENT
DESC UM_TEST_X
ID NUMBER(5)
FILENAME VARCHAR2(100)
XMLLINE CLOB

STEP2: RUN THE BELOW SQL
WITH t AS (
SELECT xmltype(
xmlline
) doc from IF_EGYUAT4.UM_TEST_X )
SELECT x.message_id
FROM t, xmltable('//pacs.008.001.01' passing t.doc
columns
message_id VARCHAR2(20) path 'MsgId'
) x;

I GOT THE ATTACHED ERROR, CAN YOU PLEASE TELL ME, WHAT'S WENT WRONG


ERROR at line 2:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00216: invalid character 133 (0x85)
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1




<?xml version="1.0"?><Document xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.01"> <pacs.008.001.01> <GrpHdr> <MsgId>ACH-23032009-14688</MsgId> <CreDtTm>2009-03-23T09:36:35.671875+02:00</CreDtTm> <NbOfTxs>5</NbOfTxs> <TtlIntrBkSttlmAmt Ccy="EGP">500</TtlIntrBkSttlmAmt> <IntrBkSttlmDt>2009-03-23</IntrBkSttlmDt> <SttlmInf> <SttlmMtd>CLRG</SttlmMtd> <ClrSys> <ClrSysId>PEG</ClrSysId> </ClrSys> </SttlmInf> <InstdAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> </InstdAgt> </GrpHdr> <CdtTrfTxInf> <PmtId> <EndToEndId>TX20090323000012</EndToEndId> <TxId>TX20090323000012</TxId> </PmtId> <IntrBkSttlmAmt Ccy="EGP">100</IntrBkSttlmAmt> <ChrgBr>SLEV</ChrgBr> <Dbtr> <Nm>مكتب الوزير</Nm> <Id> <PrvtId> <CstmrNb>10100001</CstmrNb> </PrvtId> </Id> </Dbtr> <DbtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> </DbtrAgt> <DbtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </DbtrAgtAcct> <CdtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> <BrnchId> <Id>2001</Id> </BrnchId> </CdtrAgt> <CdtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </CdtrAgtAcct> <Cdtr> <Nm>أحمد حسين إمام الدين إبراهيم</Nm> <Id> <PrvtId> <MplyrIdNb>10000002573827</MplyrIdNb> </PrvtId> </Id> </Cdtr> <CdtrAcct> <Id> <PrtryAcct> <Id>9818032200010011</Id> </PrtryAcct> </Id> <Tp> <Cd>SLRY</Cd> </Tp> </CdtrAcct> <Purp> <Prtry>0001</Prtry> </Purp> <RmtInf> <Ustrd>00010001000066777777770000000555555666666</Ustrd> </RmtInf> </CdtTrfTxInf> <CdtTrfTxInf> <PmtId> <EndToEndId>TX20090323000013</EndToEndId> <TxId>TX20090323000013</TxId> </PmtId> <IntrBkSttlmAmt Ccy="EGP">100</IntrBkSttlmAmt> <ChrgBr>SLEV</ChrgBr> <Dbtr> <Nm>مكتب الوزير</Nm> <Id> <PrvtId> <CstmrNb>10100001</CstmrNb> </PrvtId> </Id> </Dbtr> <DbtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> </DbtrAgt> <DbtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </DbtrAgtAcct> <CdtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> <BrnchId> <Id>2001</Id> </BrnchId> </CdtrAgt> <CdtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </CdtrAgtAcct> <Cdtr> <Nm>عادل كمال أحمد سلامه الطويل</Nm> <Id> <PrvtId> <MplyrIdNb>10000002573828</MplyrIdNb> </PrvtId> </Id> </Cdtr> <CdtrAcct> <Id> <PrtryAcct> <Id>9818032200010029</Id> </PrtryAcct> </Id> <Tp> <Cd>SLRY</Cd> </Tp> </CdtrAcct> <Purp> <Prtry>0001</Prtry> </Purp> <RmtInf> <Ustrd>00010001000066777777770000000555555666666</Ustrd> </RmtInf> </CdtTrfTxInf> <CdtTrfTxInf> <PmtId> <EndToEndId>TX20090323000014</EndToEndId> <TxId>TX20090323000014</TxId> </PmtId> <IntrBkSttlmAmt Ccy="EGP">100</IntrBkSttlmAmt> <ChrgBr>SLEV</ChrgBr> <Dbtr> <Nm>مكتب الوزير</Nm> <Id> <PrvtId> <CstmrNb>10100001</CstmrNb> </PrvtId> </Id> </Dbtr> <DbtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> </DbtrAgt> <DbtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </DbtrAgtAcct> <CdtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> <BrnchId> <Id>2001</Id> </BrnchId> </CdtrAgt> <CdtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </CdtrAgtAcct> <Cdtr> <Nm>سوزان كمال بطرس جرجس حنا</Nm> <Id> <PrvtId> <MplyrIdNb>10000002573829</MplyrIdNb> </PrvtId> </Id> </Cdtr> <CdtrAcct> <Id> <PrtryAcct> <Id>9818032200010037</Id> </PrtryAcct> </Id> <Tp> <Cd>SLRY</Cd> </Tp> </CdtrAcct> <Purp> <Prtry>0001</Prtry> </Purp> <RmtInf> <Ustrd>00010001000066777777770000000555555666666</Ustrd> </RmtInf> </CdtTrfTxInf> <CdtTrfTxInf> <PmtId> <EndToEndId>TX20090323000015</EndToEndId> <TxId>TX20090323000015</TxId> </PmtId> <IntrBkSttlmAmt Ccy="EGP">100</IntrBkSttlmAmt> <ChrgBr>SLEV</ChrgBr> <Dbtr> <Nm>مكتب الوزير</Nm> <Id> <PrvtId> <CstmrNb>10100001</CstmrNb> </PrvtId> </Id> </Dbtr> <DbtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> </DbtrAgt> <DbtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </DbtrAgtAcct> <CdtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> <BrnchId> <Id>2001</Id> </BrnchId> </CdtrAgt> <CdtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </CdtrAgtAcct> <Cdtr> <Nm>محمد السيد أحمد عبده حسن</Nm> <Id> <PrvtId> <MplyrIdNb>10000002573830</MplyrIdNb> </PrvtId> </Id> </Cdtr> <CdtrAcct> <Id> <PrtryAcct> <Id>9818032200010045</Id> </PrtryAcct> </Id> <Tp> <Cd>SLRY</Cd> </Tp> </CdtrAcct> <Purp> <Prtry>0001</Prtry> </Purp> <RmtInf> <Ustrd>00010001000066777777770000000555555666666</Ustrd> </RmtInf> </CdtTrfTxInf> <CdtTrfTxInf> <PmtId> <EndToEndId>TX20090323000016</EndToEndId> <TxId>TX20090323000016</TxId> </PmtId> <IntrBkSttlmAmt Ccy="EGP">100</IntrBkSttlmAmt> <ChrgBr>SLEV</ChrgBr> <Dbtr> <Nm>مكتب الوزير</Nm> <Id> <PrvtId> <CstmrNb>10100001</CstmrNb> </PrvtId> </Id> </Dbtr> <DbtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> </DbtrAgt> <DbtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </DbtrAgtAcct> <CdtrAgt> <FinInstnId> <ClrSysMmbId> <Id>8</Id> </ClrSysMmbId> </FinInstnId> <BrnchId> <Id>2001</Id> </BrnchId> </CdtrAgt> <CdtrAgtAcct> <Id> <PrtryAcct> <Id>4003075232</Id> </PrtryAcct> </Id> </CdtrAgtAcct> <Cdtr> <Nm>29792 Mohamed Arfa</Nm> <Id> <PrvtId> <MplyrIdNb>10023502573830</MplyrIdNb> </PrvtId> </Id> </Cdtr> <CdtrAcct> <Id> <PrtryAcct> <Id>9818032205600045</Id> </PrtryAcct> </Id> <Tp> <Cd>SLRY</Cd> </Tp> </CdtrAcct> <Purp> <Prtry>0001</Prtry> </Purp> <RmtInf> <Ustrd>00010001000066777777770000000555555666666</Ustrd> </RmtInf> </CdtTrfTxInf> </pacs.008.001.01></Document>



regards



Re: ora 31011 xml parsing failed lpx 00216 [message #470265 is a reply to message #470211] Mon, 09 August 2010 08:01 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
ORA-31011: 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.
 

Re: ora 31011 xml parsing failed lpx 00216 [message #470272 is a reply to message #470265] Mon, 09 August 2010 08:11 Go to previous messageGo to next message
abcraj
Messages: 5
Registered: August 2010
Location: BAHRAIN
Junior Member

thanks for the reply,

it's a valid document. i can open this document on IE

regards
Re: ora 31011 xml parsing failed lpx 00216 [message #470274 is a reply to message #470272] Mon, 09 August 2010 08:15 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
abcraj wrote on Mon, 09 August 2010 08:11

thanks for the reply,

it's a valid document. i can open this document on IE

regards


you can open any document in IE which can be open in notepad Smile it does not mean that is valid, Tye similer doc and compare both.
Re: ora 31011 xml parsing failed lpx 00216 [message #470284 is a reply to message #470274] Mon, 09 August 2010 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 66205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, Oracle XML support and IE XML support is not the same one.

What is your database character set?
What is your Oracle version (4 decimals)?

Regards
Michel

[Updated on: Mon, 09 August 2010 08:35]

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470292 is a reply to message #470211] Mon, 09 August 2010 08:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Where exactly are you getting this error?
Your file is a valid xml file.
I was able to load it into table and as expected get junk value for elements <Nm>.
As Michel suggested, problem could be your character set.

[Updated on: Mon, 09 August 2010 08:43]

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470447 is a reply to message #470292] Tue, 10 August 2010 00:17 Go to previous messageGo to next message
abcraj
Messages: 5
Registered: August 2010
Location: BAHRAIN
Junior Member

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET AR8ISO8859P6
NLS_RDBMS_VERSION 10.2.0.4.0

could you please help to solve this issue ?
Re: ora 31011 xml parsing failed lpx 00216 [message #470449 is a reply to message #470447] Tue, 10 August 2010 00:45 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
did you tried What I said ?
Re: ora 31011 xml parsing failed lpx 00216 [message #470450 is a reply to message #470292] Tue, 10 August 2010 00:47 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Mahesh Rajendran wrote on Mon, 09 August 2010 08:43

Your file is a valid xml file.
I was able to load it into table and as expected get junk value


So , you file proble is solved. Since that is valid , so you problem could be as Michel suggested,
Re: ora 31011 xml parsing failed lpx 00216 [message #470451 is a reply to message #470450] Tue, 10 August 2010 01:02 Go to previous messageGo to next message
abcraj
Messages: 5
Registered: August 2010
Location: BAHRAIN
Junior Member
rahul,

i have tried to load a similar file and still i am facing the same problem. i tried a simple xml file , it's working fine.

how to solve this character set problem ?

Re: ora 31011 xml parsing failed lpx 00216 [message #470464 is a reply to message #470451] Tue, 10 August 2010 01:23 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Take a look at Choosing a Character Set
Re: ora 31011 xml parsing failed lpx 00216 [message #470467 is a reply to message #470451] Tue, 10 August 2010 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 66205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Change the header of XML as:
=<?xml version="1.0" encoding="AR8ISO8859P6"?>
and tell us what happens.

Regards
Michel
Re: ora 31011 xml parsing failed lpx 00216 [message #470470 is a reply to message #470467] Tue, 10 August 2010 01:42 Go to previous message
abcraj
Messages: 5
Registered: August 2010
Location: BAHRAIN
Junior Member
hi,

i changed the header of the xml,and now the error is different

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00283: document encoding is UTF-8-based but default input encoding is not
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1

Regards

Previous Topic: Display Ref cursor in a procedure
Next Topic: SQL Plus goes not responding
Goto Forum:
  


Current Time: Sat Feb 23 06:37:10 CST 2019