0 Replies Latest reply: Mar 22, 2012 7:02 AM by 783602 RSS

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

    783602
      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