Database Software

Hi ,

I was trying to load data from XML files to an Oracle database table.

I followed these below steps to load that file data into a table.

Created XML_DIR1 as oracle directory where i have kept all XML files.

Create table import_rpt_xml of xmltype

xmltype store as binary xml;

insert into import_rpt_xml

values (

xmltype (

bfilename('XML_DIR1','I-Yamanouchi-20040525-501.xml'),

nls_charset_id('AL32UTF8')

)

);

This insert shows below error:

Error starting at line 80 in command:

insert into import_rpt_xml

values(

xmltype(

bfilename('XML_DIR1', 'I-Yamanouchi-20040525-501.SGM')

, nls_charset_id('AL32UTF8')

)

)

Error report:

SQL Error: ORA-31061: XDB error: XML event error

ORA-19202: Error occurred in XML processing

In line 69 of orastream:

LPX-00217: invalid character 142 (U+008E)

I tried to look into my XML and got that it has some Japanese characters in it.

Could anybody help me on this to deal with japanese characters in XML. I don't want to miss those characters.

My databse NLS_CHARACTERSET is 'AL32UTF8'.

My sample XML file looks like this.

<ichicsr lang="ja">

    <ichicsrmessageheader>

      <messagetype>ichicsr</messagetype>

      <messageformatversion>2.1</messageformatversion>

      <messageformatrelease>2.0</messageformatrelease>

      <messagenumb>US-Yamanouchi-W2004050033-4</messagenumb>

      <messagesenderidentifier>Yamanouchi</messagesenderidentifier>

      <messagereceiveridentifier>PMDA</messagereceiveridentifier>

      <messagedateformat>204</messagedateformat>

      <messagedate>20040525100000</messagedate>

    </ichicsrmessageheader>

    <safetyreport>

      <safetyreportversion>4</safetyreportversion>

      <safetyreportid>US-Yamanouchi-2004003040Lip</safetyreportid>

      <primarysourcecountry>US</primarysourcecountry>

      <occurcountry>US</occurcountry>

      <transmissiondateformat>102</transmissiondateformat>

      <transmissiondate>20040525</transmissiondate>

      <reporttype>1</reporttype>

      <serious>1</serious>

      <seriousnessdeath></seriousnessdeath>

      <seriousnesslifethreatening></seriousnesslifethreatening>

      <seriousnesshospitalization>1</seriousnesshospitalization>

      <seriousnessdisabling></seriousnessdisabling>

      <seriousnesscongenitalanomali></seriousnesscongenitalanomali>

      <seriousnessother>1</seriousnessother>

      <receivedateformat>102</receivedateformat>

      <receivedate>20040311</receivedate>

      <receiptdateformat>102</receiptdateformat>

      <receiptdate>20040506</receiptdate>

      <additionaldocument>2</additionaldocument>

      <documentlist></documentlist>

      <fulfillexpeditecriteria>1</fulfillexpeditecriteria>

      <authoritynumb></authoritynumb>

      <companynumb>US-Yamanouchi-2004003040</companynumb>

      <duplicate></duplicate>

      <casenullification></casenullification>

      <nullificationreason></nullificationreason>

      <medicallyconfirm></medicallyconfirm>

      <reportduplicate>

        <duplicatesource></duplicatesource>

        <duplicatenumb></duplicatenumb>

      </reportduplicate>

      <linkedreport>

        <linkreportnumb></linkreportnumb>

      </linkedreport>

      <primarysource>

        <reportertitle></reportertitle>

        <reportergivename></reportergivename>

        <reportermiddlename></reportermiddlename>

        <reporterfamilyname></reporterfamilyname>

        <reporterorganization></reporterorganization>

        <reporterdepartment></reporterdepartment>

        <reporterstreet></reporterstreet>

        <reportercity></reportercity>

        <reporterstate></reporterstate>

        <reporterpostcode></reporterpostcode>

        <reportercountry>US</reportercountry>

        <qualification>1</qualification>

        <literaturereference></literaturereference>

        <studyname></studyname>

        <sponsorstudynumb></sponsorstudynumb>

        <observestudytype></observestudytype>

      </primarysource>

      <sender>

        <sendertype>1</sendertype>

        <senderorganization>Yamanouchi</senderorganization>

        <senderdepartment></senderdepartment>

        <sendertitle>Žæ’÷–ðŽÐ’·</sendertitle>

        <sendergivename>“oˆê</sendergivename>

        <sendermiddlename></sendermiddlename>

        <senderfamilyname>’|’†</senderfamilyname>

        <senderstreetaddress>“ú–{‹´–{’¬2-3-11</senderstreetaddress>

        <sendercity>’†‰›‹æ</sendercity>

        <senderstate>“Œ‹ž“s</senderstate>

        <senderpostcode></senderpostcode>

        <sendercountrycode>JP</sendercountrycode>

        <sendertel>359165493</sendertel>

        <sendertelextension></sendertelextension>

        <sendertelcountrycode>81</sendertelcountrycode>

        <senderfax>359165608</senderfax>

        <senderfaxextension></senderfaxextension>

        <senderfaxcountrycode>81</senderfaxcountrycode>

        <senderemailaddress>sfid_e2b@yamanouchi.co.jp</senderemailaddress>

      </sender>

      <receiver>

        <receivertype>2</receivertype>

        <receiverorganization>“Æ—§&#141;s&#144;­–@&#144;lˆã–ò•iˆã—Ë@Ší‘&#141;&#141;‡‹@&#141;\</receiverorganization>

        <receiverdepartment></receiverdepartment>

        <receivertitle>—&#157;Ž–’·</receivertitle>

        <receivergivename>&#143;²</receivergivename>

        <receivermiddlename></receivermiddlename>

        <receiverfamilyname>‹{“‡</receiverfamilyname>

        <receiverstreetaddress>‰à‚ªŠÖ3-3-2</receiverstreetaddress>

        <receivercity>&#144;ç‘ã“c‹æ</receivercity>

        <receiverstate>“Œ‹ž“s</receiverstate>

........and so on.

Please help me to crack this problem.

Regards ,

Vinayak