This discussion is archived
6 Replies Latest reply: Jun 18, 2012 9:29 AM by 878240 RSS

Sqlldr errors loading XML file

878240 Newbie
Currently Being Moderated
I am getting two errors on two different data elements trying to load an XML file. DB version is 11.2.0.2.0 - 64bit.

This is part of the instance file:

<?xml version="1.0" encoding="utf-8"?>
<SamseAMSS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://SAMSE-AMSS.org/namespace">
<FileInfo>
.
.
.

<AviationUIC>
<OrigUnit>
<UIC>WV7PB0</UIC>
<UnitName>B TRP 1-230TH ACS</UnitName>
<OrgLocCde>A</OrgLocCde>
<DPICode>Need LOGSA DPICode</DPICode>
<IsReporter>true</IsReporter>
<UnitPOC />
<POCNbr>(865)985-4634</POCNbr>
<POCEmail />
</OrigUnit>
<AMSSUtilCde>
<UtilCde>0</UtilCde>
<Summary>
<EIC>ROC</EIC>
*<MDS xsi:type="xsd:string">OH-58D</MDS>*
*<Model>OH-58D</Model>*
<QtyAuth>6</QtyAuth>
<QtyOH>6</QtyOH>
<RptTimeCde>H</RptTimeCde>
</Summary>
</AMSSUtilCde>
</AviationUIC>

The problem is with the MDS and Model elements.

This is part of the schema:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns="http://SAMSE-AMSS.org/namespace" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
targetNamespace="http://SAMSE-AMSS.org/namespace"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:smse="http://samsecommon.xsd/namespace"
elementFormDefault="qualified" attributeFormDefault="unqualified">
<xsd:import namespace="http://samsecommon.xsd/namespace" schemaLocation="samsecommon.xsd"/>
<xs:element name="SamseAMSS" xdb:defaultTable="SAMSEAMSS">
<xs:sequence>
<xs:element name="FileInfo" smse:type="SamseFileInfoType"/>
<xs:element name="AviationUIC" maxOccurs="unbounded">
<xs:annotation>
<xs:documentation>1 element for each UIC being reported</xs:documentation>
</xs:annotation>
<xs:complexType xdb:SQLType="AMSSUIC_T">
<xs:sequence>
.
.
.
<xs:element name="AMSSUtilCde" minOccurs="0" maxOccurs="unbounded">
<xs:annotation>
<xs:documentation>1 element for each UtilCde being reported for this UIC</xs:documentation>
</xs:annotation>
<xs:complexType xdb:SQLType="AMSSUTILCDE_T">
<xs:sequence>
<xs:element name="UtilCde">
<xs:annotation>
<xs:documentation>Key Field</xs:documentation>
</xs:annotation>
<xs:simpleType>
<xs:restriction base="xs:token">
<xs:length value="1"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Summary" maxOccurs="unbounded">
<xs:annotation>
<xs:documentation>1 element per type of equipment (by EIC / PrimeEIC) authorized for this UIC -- always sent every time.</xs:documentation>
</xs:annotation>
<xs:complexType xdb:SQLType="SUMMARY_T">
<xs:sequence>
<xs:element name="EIC" minOccurs="0" smse:type="EICType">
<xs:annotation>
<xs:documentation>Key Field</xs:documentation>
</xs:annotation>
</xs:element>
*<MDS xsi:type="xsd:string">OH-58D</MDS>*
*<Model>OH-58D</Model>*
               <xs:element name="PrimeEIC" minOccurs="0" smse:type="EICType" nillable="true">
<xs:annotation>
<xs:documentation>Key Field</xs:documentation>
</xs:annotation>
</xs:element>
<xs:element name="QtyAuth" type="xs:unsignedShort"/>

First, it doesn't recognize the "Model" data element that is defined in the schema:

Record 3: Rejected - Error on table SAMSEAMSS.
ORA-30937: No schema definition for 'Model' (namespace 'http://SAMSE-AMSS.org/namespace') in parent '/SamseAMSS/AviationUIC[1]/AMSSUtilCde[1]/Summary[1]'

If I remove the Model from the instance file and try to load, it does not recognize the xsi:type in the MDS element. I also tried using xsi:type in the schema definition.

Record 1: Rejected - Error on table SAMSEAMSS.
ORA-31079: unable to resolve reference to type "string"

If I change xsi:type to xsd:type it loads with no errors. However we don't control the incoming XML files so it needs to load with xsi:type.

Thanks for your help.
  • 1. Re: Sqlldr errors loading XML file
    odie_63 Guru
    Currently Being Moderated
    Welcome back ;)
    First, it doesn't recognize the "Model" data element that is defined in the schema
    How are Model and MDS defined in the schema?

    Don't tell me you've simply pasted this in the middle of the schema :
    *<MDS xsi:type="xsd:string">OH-58D</MDS>*
    *<Model>OH-58D</Model>*
    ?
  • 2. Re: Sqlldr errors loading XML file
    878240 Newbie
    Currently Being Moderated
    Whoops no I didn't put that in the schema definition! This is the part with those elements:

    <xs:complexType xdb:SQLType="AMSSUTILCDE_T">
    <xs:sequence>
    <xs:element name="UtilCde">
    <xs:annotation>
    <xs:documentation>Key Field</xs:documentation>
    </xs:annotation>
    <xs:simpleType>
    <xs:restriction base="xs:token">
    <xs:length value="1"/>
    </xs:restriction>
    </xs:simpleType>
    </xs:element>
    <xs:element name="Summary" maxOccurs="unbounded">
    <xs:annotation>
    <xs:documentation>1 element per type of equipment (by EIC / PrimeEIC) authorized for this UIC -- always sent every time.</xs:documentation>
    </xs:annotation>
    <xs:complexType xdb:SQLType="SUMMARY_T">
    <xs:sequence>
    <xs:element name="EIC" smse:type="EICType">
    <xs:annotation>
    <xs:documentation>Key Field</xs:documentation>
    </xs:annotation>
    </xs:element>
                   *<xs:element name="MDS" minOccurs="0" type="xsd:string"/>*
                   *<xs:element name="Model" minOccurs="0" type="xsd:string"/>*
                   <xs:element name="PrimeEIC" smse:type="EICType" nillable="true">
    <xs:annotation>
    <xs:documentation>Key Field</xs:documentation>
    </xs:annotation>
    </xs:element>

    Thanks.

    Edited by: user4109719 on Jun 12, 2012 6:32 AM Put correct Model definition

    Edited by: user4109719 on Jun 12, 2012 6:37 AM
  • 3. Re: Sqlldr errors loading XML file
    odie_63 Guru
    Currently Being Moderated
    OK.

    What does this mean?
    However we don't control the incoming XML files so it needs to load with xsi:type.
    Why do you need xsi:type?
  • 4. Re: Sqlldr errors loading XML file
    878240 Newbie
    Currently Being Moderated
    It has that in the instance file.

    This is how it starts.

    <?xml version="1.0" encoding="utf-8"?>
    <SamseAMSS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://SAMSE-AMSS.org/namespace">

    The MDS element references it.

    *<MDS xsi:type="xsd:string">OH-58D</MDS>*


    From what I could tell from some web searching the "http://www.w3.org/2001/XMLSchema-instance" namespace has a "type".

    I tried to put the xsi:type designation in the schema definition but it doesn't recognize it then either.
  • 5. Re: Sqlldr errors loading XML file
    odie_63 Guru
    Currently Being Moderated
    The MDS element references it.

    *<MDS xsi:type="xsd:string">OH-58D</MDS>*
    It's used for local (re)definition of an element type, provided the locally-defined type is based on the original type from the schema.
    In this case, I don't see any reason why an instance document should exhibit this attribute, since it's already of that datatype in the schema.
  • 6. Re: Sqlldr errors loading XML file
    878240 Newbie
    Currently Being Moderated
    Why is it generating an error? We may get instance files with that. Is there something that could go in the schema that would prevent an error?

    Thanks.

Legend

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