SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How can I Parse the XML file using Oracle Sql query.

900980Aug 13 2012 — edited Aug 13 2012

Dear all,
I have a XML file which have to parse and show the result as per the below example
Can you please recommend me an approach to get the output.

Here is my XML for example:

<?xml version="1.0" encoding="UTF-8"?>
<pi:Extract_Employees xmlns:pi="urn:com.workday/picof">
<pi:Employee>
<pi:Additional_Information><pi:Job_Title pi:PriorValue="">Intern - Masterï¿½s</pi:Job_Title>
</pi:Additional_Information>
</pi:Employee>
</pi:Extract_Employees>

Database Information:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

SQL > SELECT * FROM NLS_DATABASE_PARAMETERS;

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
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
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 10.2.0.3.0

The above Xml file having UTF-8 character sets which is multi byte,

But in my database character set is WE8ISO8859P1 i.e. ISO-8859-1 (single byte character set)

SQL > SELECT extractValue(Value(x), '/pi:Employee/pi:Additional_Information/pi:Job_Title','xmlns:pi="urn:com.workday/picof"')
FROM table(XMLSequence(extract(XMLType(bfilename('XMLDIR','XML_Issue_227176.xml'), nls_charset_id('AL32UTF8')),'/pi:Employee','xmlns:pi="urn:com.workday/picof"'))) x;

giving the following error:

Error:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00200: could not convert from encoding UTF-8 to ISO-8859-1
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 295
ORA-06512: at line 1

Also I have tried with this
SQL > SELECT convert(extractValue(Value(x), '/pi:Employee/pi:Additional_Information/pi:Job_Title','xmlns:pi="urn:com.workday/picof"'),'WE8ISO8859P1', 'UTF8')
FROM table(XMLSequence(extract(XMLType(bfilename('XMLDIR','XML_Issue_227176.xml'), nls_charset_id('AL32UTF8')),'/pi:Employee','xmlns:pi="urn:com.workday/picof"'))) x;

The same error getting as per the above error message.

Please help in this regard.

Thanks & regards,
Prasanta

This post has been answered by odie_63 on Aug 13 2012

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 10 2012

Added on Aug 13 2012

#sql

7 comments

2,514 views