6 Replies Latest reply: Jun 18, 2012 11:29 AM by 878240 RSS

    Sqlldr errors loading XML file

    878240
      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
          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
            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
              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
                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
                  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
                    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.