OraFAQ Forum: SQL & PL/SQL » ora 31011 xml parsing failed lpx 00216

Home » SQL & PL/SQL » SQL & PL/SQL » ora 31011 xml parsing failed lpx 00216

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ora 31011 xml parsing failed lpx 00216 [message #470211]

Mon, 09 August 2010 05:57

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

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470265 is a reply to message #470211]

Mon, 09 August 2010 08:01

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.

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470272 is a reply to message #470265]

Mon, 09 August 2010 08:11

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

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470274 is a reply to message #470272]

Mon, 09 August 2010 08:15

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.

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470284 is a reply to message #470274]

Mon, 09 August 2010 08:34

Michel Cadot
Messages: 68645
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

Mahesh Rajendran
Messages: 10707
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

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 ?

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470449 is a reply to message #470447]

Tue, 10 August 2010 00:45

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

did you tried What I said ?

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470450 is a reply to message #470292]

Tue, 10 August 2010 00:47

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,

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470451 is a reply to message #470450]

Tue, 10 August 2010 01:02

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 ?

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470464 is a reply to message #470451]

Tue, 10 August 2010 01:23

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Take a look at Choosing a Character Set

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470467 is a reply to message #470451]

Tue, 10 August 2010 01:26

Michel Cadot
Messages: 68645
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

Report message to a moderator

Re: ora 31011 xml parsing failed lpx 00216 [message #470470 is a reply to message #470467]

Tue, 10 August 2010 01:42

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 22:14:13 CDT 2024