This discussion is archived
0 Replies Latest reply: Mar 22, 2012 5:02 AM by 783602 RSS

Error using sql loader for xml: LPX-00283: document encoding is UTF-8-based

783602 Newbie
Currently Being Moderated
Hello,

version - Oracle Database 10g Release 10.2.0.1.0 - Production

I am trying to load an xml document using sql loader. I have successfully managed to do this before by registering the schema and using sql loader to load in the xml documents but i guess it was more by luck than judgement. I have now been given another xml file and xsd from a client and i have registered the schema ok and have tried to load in the xml file but seem to get this error.
LPX-00283: document encoding is UTF-8-based but default input encoding is not
The xsd i have been given to use is as follows:
<?xml version="1.0" encoding="utf-16" ?> 
                      <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://Travel.Customer.Profiles.ExternalSchemas.customer_profile_extract/2010/11" elementFormDefault="qualified" targetNamespace="http://Travel.Customer.Profiles.ExternalSchemas.customer_profile_extract/2010/11" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="true">
                      <xs:simpleType name="AccountStateType">
                        <xs:annotation>
                          <xs:documentation>A state that a account can have in Charm</xs:documentation>
                        </xs:annotation>
                        <xs:restriction base="xs:string">
                          <xs:enumeration value="ACTIVE" />
                          <xs:enumeration value="CLOSED" />
                          <xs:enumeration value="INACTIVE" />
                          <xs:enumeration value="PENDING" />
                        </xs:restriction>
                      </xs:simpleType>
                      <xs:simpleType name="ProfileStateType">
                        <xs:annotation>
                          <xs:documentation>A state that a profile can have in Sloop</xs:documentation>
                        </xs:annotation>
                        <xs:restriction base="xs:string">
                          <xs:enumeration value="ACTIVE" />
                          <xs:enumeration value="CLOSED" />
                          <xs:enumeration value="INACTIVE" />
                          <xs:enumeration value="PENDING" />
                        </xs:restriction>
                      </xs:simpleType>
                      <xs:complexType name="CustomerProfile">
                        <xs:sequence>
                          <xs:element name="CustomerID" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>ID of the profile in Sloop</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="CompanyName" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Can be used for company name</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="Title" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Optional title of person</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="FirstName" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Company main contact if Company</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="Surname" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Company main contact if Company</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="IsFemale" nillable="true" type="xs:boolean">
                            <xs:annotation>
                              <xs:documentation>If person is female, used when private customer</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="EMail" nillable="true" type="xs:string" />
                          <xs:element name="DateOfBirth" nillable="true" type="xs:date" />
                          <xs:element name="Address1" nillable="true" type="xs:string" />
                          <xs:element name="Address2" nillable="true" type="xs:string" />
                          <xs:element name="PostCode" nillable="true" type="xs:string" />
                          <xs:element name="City" nillable="true" type="xs:string" />
                          <xs:element name="County" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Optional county</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="CountryCode" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>ISO Code 2: SE, UK, NL etc</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="PhoneHome" nillable="true" type="xs:string" />
                          <xs:element name="PhoneMobile" nillable="true" type="xs:string" />
                          <xs:element name="SalesOwnerID" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Sales Owner the customer profile belongs to.</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="CreatedBy" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Who created the profile</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="CreatedDate" type="xs:dateTime">
                            <xs:annotation>
                              <xs:documentation>Date when profile was created</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="UpdatedBy" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Who created the profile</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="UpdatedDate" nillable="true" type="xs:dateTime">
                            <xs:annotation>
                              <xs:documentation>Date when profile was updated</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="AllowDisturbanceSMS" type="xs:boolean" />
                          <xs:element name="DMAcceptSMS" type="xs:boolean" />
                          <xs:element name="DMAcceptMail" type="xs:boolean" />
                          <xs:element name="DMAcceptPostal" type="xs:boolean" />
                          <xs:element name="EmailAsHTML" type="xs:boolean">
                            <xs:annotation>
                              <xs:documentation>If Emails can be formated as HTML</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="ProfileStatus" type="ProfileStateType">
                            <xs:annotation>
                              <xs:documentation>Active, Closed, Inactive or Pending</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="Blocked" type="xs:boolean" />
                          <xs:element name="DateBlocked" nillable="true" type="xs:dateTime" />
                          <xs:element name="LoyaltyNumber" nillable="true" type="xs:int">
                            <xs:annotation>
                              <xs:documentation>Alson kown as Compass Point member ID.</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyJoinDate" nillable="true" type="xs:date">
                            <xs:annotation>
                              <xs:documentation>Date when member joined loyalty program</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyBalance" nillable="true" type="xs:int">
                            <xs:annotation>
                              <xs:documentation>Current balance on loyalty account.</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyStatus" nillable="true" type="AccountStateType">
                            <xs:annotation>
                              <xs:documentation>Active, Closed, Inactive or Pending</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyValidFromDate" nillable="true" type="xs:date">
                            <xs:annotation>
                              <xs:documentation>Date from when account is available</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyValidToDate" nillable="true" type="xs:date">
                            <xs:annotation>
                              <xs:documentation>Date to when account is aviable</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyAccountType" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Type of account, i.e Blue, Gold etc.</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyCreatedBy" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Who created the account</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyCreatedDate" nillable="true" type="xs:dateTime">
                            <xs:annotation>
                              <xs:documentation>Date when account was created</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyUpdatedBy" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Who created the Account</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="LoyaltyUpdatedDate" nillable="true" type="xs:dateTime">
                            <xs:annotation>
                              <xs:documentation>Date when account was updated</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="PreferredRoute" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>Route code</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="PreferredLanguageCode" nillable="true" type="xs:string">
                            <xs:annotation>
                              <xs:documentation>2 char language code</xs:documentation>
                            </xs:annotation>
                          </xs:element>
                          <xs:element name="NoteText" nillable="true" type="xs:string" />
                          <xs:element name="NoteDateTime" nillable="true" type="xs:dateTime" />
                          <xs:element name="NoteBy" nillable="true" type="xs:string" />
                        </xs:sequence>
                      </xs:complexType>
                      <xs:element name="CustomerProfiles" xdb:defaultTable="'||p_xml_table_name||'">
                        <xs:annotation>
                          <xs:documentation>Comment describing your root element</xs:documentation>
                        </xs:annotation>
                        <xs:complexType>
                          <xs:sequence>
                            <xs:element maxOccurs="unbounded" name="CustomerProfile" type="CustomerProfile" />
                          </xs:sequence>
                        </xs:complexType>
                       </xs:element>
                     </xs:schema>
and an example of the xml file that i receive is :
<ns0:CustomerProfiles xmlns:ns0="http://Travel.Customer.Profiles.ExternalSchemas.customer_profile_extract/2010/11" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <ns0:CustomerProfile>
          <ns0:CustomerID>xxxxx</ns0:CustomerID>
          <ns0:CompanyName xsi:nil="true"/>
          <ns0:Title>MRS</ns0:Title>
     </ns0:CustomerProfile>
</ns0:CustomerProfiles>
If i insert the line
<?xml version="1.0" encoding="utf-8" ?>
manually at the top of the xml file and load it the file loads fine. Is there an easy solution or do i need the client to add this line before they send it?

I appologise in advance is the answer is obvious :)

Cheers,

Dan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points