Home > Net >  Error Importing XML using SSIS XML Source maxOccurs=1 on element
Error Importing XML using SSIS XML Source maxOccurs=1 on element

Time:03-09

When attempting to import XML data using an SSIS package, I keep getting this error :

The XML Source was unable to process the XML data. The Xml source document contains multiple "CaseCode" elements and maxOccurs=1 was specified in the Xml schema.

I'm using the following sample data:

<?xml version="1.0" encoding="UTF-8"?>
<Cases>
    <Case>
        <CaseCode>6789</CaseCode>
        <EthicsPoint_CaseID>0</EthicsPoint_CaseID>
        <TierName>Legacy</TierName>
        <ReportNumber>Sample-2018-1-5</ReportNumber>
        <PrimaryIssueType>Inquiry</PrimaryIssueType>
        <CaseStatus>Unreviewed</CaseStatus>
        <AlertStatus></AlertStatus>
        <OrgName></OrgName>
        <LocationAddress></LocationAddress>
        <LocationCity></LocationCity>
        <LocationState></LocationState>
        <LocationPostalCode></LocationPostalCode>
        <LocationCountry></LocationCountry>
        <Details>Question about the policy on attire.</Details>
        <CaseCreator>Caelum, Noctis Lucis</CaseCreator>
        <PrimaryOutcome>- Select One -</PrimaryOutcome>
        <Secondary1Outcome>- Select One -</Secondary1Outcome>
        <Secondary2Outcome>- Select One -</Secondary2Outcome>
        <ActionTaken>- Select One -</ActionTaken>
        <DateOpened>2017-10-15T00:00:00</DateOpened>
        <DateUpdated>2018-01-11T12:59:24</DateUpdated>
        <Language>English</Language>
        <IsReporterEmployee></IsReporterEmployee>
        <IntakeMethod>Phone</IntakeMethod>
        <ReporterAnonymous>Yes</ReporterAnonymous>
        <ReporterFirstName></ReporterFirstName>
        <ReporterLastName></ReporterLastName>
        <ReporterPhone></ReporterPhone>
        <ReporterEmail></ReporterEmail>
        <Edited>0</Edited>
        <HasOriginal>0</HasOriginal>
        <CaseType>Inquiry</CaseType>
        <IsViolation>No</IsViolation>
        <CaseConfidential>0</CaseConfidential>
        <Summary></Summary>
        <PolicyAffected>0</PolicyAffected>
        <ControlFailure>0</ControlFailure>
        <Secondary1IssueType></Secondary1IssueType>
        <Secondary2IssueType></Secondary2IssueType>
        <PolicyViolation>0</PolicyViolation>
        <ComplianceViolationType>0</ComplianceViolationType>
        <DataPrivacy>No</DataPrivacy>
        <ReporterParticipantCode>0</ReporterParticipantCode>
        <TotalItemLoss>0.000000000000000e 000</TotalItemLoss>
        <TotalItemDamage>0.000000000000000e 000</TotalItemDamage>
        <DataPrivacyReason>0</DataPrivacyReason>
        <AssigneeOnly>0</AssigneeOnly>
        <LocationName></LocationName>
        <BranchNumber></BranchNumber>
        <IsHotline>No</IsHotline>
        <Participant>
            <CaseCode>6789</CaseCode>
            <ParticipantCode>6910</ParticipantCode>
            <FirstName>Mary</FirstName>
            <MiddleName/>
            <LastName>Tudor</LastName>
            <PhoneNumber/>
            <Email/>
            <HRCode/>
            <Badge/>
            <Relationship>Employee</Relationship>
            <Role>Reporter</Role>
            <PrimaryParticipantResult>No Action Necessary</PrimaryParticipantResult>
            <Secondary1ParticipantResult>No Action Necessary</Secondary1ParticipantResult>
            <Secondary2ParticipantResult>No Action Necessary</Secondary2ParticipantResult>
        </Participant>
        <CaseHistory>
            <CaseCode>6789</CaseCode>
            <TrackTypeName>Added File</TrackTypeName>
            <TrackUpdatedDate>2018-01-11T12:58:56.063</TrackUpdatedDate>
            <TrackAnonymous>0</TrackAnonymous>
            <TrackComment>fluffy-bunny.jpeg</TrackComment>
            <Guest>0</Guest>
            <TrackSourceName>User</TrackSourceName>
            <UserName>Caelum, Noctis Lucis</UserName>
        </CaseHistory>
        <CaseHistory>
            <CaseCode>6789</CaseCode>
            <TrackTypeName>Added File</TrackTypeName>
            <TrackUpdatedDate>2018-01-11T12:59:01.160</TrackUpdatedDate>
            <TrackAnonymous>0</TrackAnonymous>
            <TrackComment>Huge Fluffy Dog.jpg</TrackComment>
            <Guest>0</Guest>
            <TrackSourceName>User</TrackSourceName>
            <UserName>Caelum, Noctis Lucis</UserName>
        </CaseHistory>
        <CaseHistory>
            <CaseCode>6789</CaseCode>
            <TrackTypeName>Added File</TrackTypeName>
            <TrackUpdatedDate>2018-01-11T12:59:06.233</TrackUpdatedDate>
            <TrackAnonymous>0</TrackAnonymous>
            <TrackComment>Test file.docx</TrackComment>
            <Guest>0</Guest>
            <TrackSourceName>User</TrackSourceName>
            <UserName>Caelum, Noctis Lucis</UserName>
        </CaseHistory>
        <CaseHistory>
            <CaseCode>6789</CaseCode>
            <TrackTypeName>Added File</TrackTypeName>
            <TrackUpdatedDate>2018-01-11T12:59:11.427</TrackUpdatedDate>
            <TrackAnonymous>0</TrackAnonymous>
            <TrackComment>The Raven.pdf</TrackComment>
            <Guest>0</Guest>
            <TrackSourceName>User</TrackSourceName>
            <UserName>Caelum, Noctis Lucis</UserName>
        </CaseHistory>
        <CaseHistory>
            <CaseCode>6789</CaseCode>
            <TrackTypeName>Viewed Case</TrackTypeName>
            <TrackUpdatedDate>2018-01-11T13:20:20.317</TrackUpdatedDate>
            <TrackAnonymous>0</TrackAnonymous>
            <TrackComment/>
            <Guest>0</Guest>
            <TrackSourceName>User</TrackSourceName>
            <UserName>Caelum, Noctis Lucis</UserName>
        </CaseHistory>
        <CaseHistory>
            <CaseCode>6789</CaseCode>
            <TrackTypeName>Viewed Case</TrackTypeName>
            <TrackUpdatedDate>2018-01-18T10:06:06.610</TrackUpdatedDate>
            <TrackAnonymous>0</TrackAnonymous>
            <TrackComment/>
            <Guest>0</Guest>
            <TrackSourceName>User</TrackSourceName>
            <UserName>Smith, Jane</UserName>
        </CaseHistory>
        <CaseHistory>
            <CaseCode>6789</CaseCode>
            <TrackTypeName>Viewed Case</TrackTypeName>
            <TrackUpdatedDate>2018-02-23T13:26:19.373</TrackUpdatedDate>
            <TrackAnonymous>0</TrackAnonymous>
            <TrackComment/>
            <Guest>0</Guest>
            <TrackSourceName>User</TrackSourceName>
            <UserName>Specialist, Technical</UserName>
        </CaseHistory>
        <AccessBlacklist>
            <CaseCode>6789</CaseCode>
            <UserName>Bennett, Eliza</UserName>
            <BlacklistDate>2018-01-11T12:59:24</BlacklistDate>
        </AccessBlacklist>
        <AccessBlacklist>
            <CaseCode>6789</CaseCode>
            <UserName>Lionheart, Squall</UserName>
            <BlacklistDate>2018-01-11T12:59:24</BlacklistDate>
        </AccessBlacklist>
        <Attachments>
            <CaseCode>6789</CaseCode>
            <FileCode>170</FileCode>
            <RestitutionCode>0</RestitutionCode>
            <FileType>image/jpeg</FileType>
            <DateUploaded>2018-01-11T12:58:56.040</DateUploaded>
            <Key/>
            <FileName>170-fluffy-bunny.jpeg</FileName>
            <Size>19870</Size>
            <FileCategoryCode>Other</FileCategoryCode>
            <Original_Description/>
            <TranslationNeeded_Description>0</TranslationNeeded_Description>
            <EthicsPoint_CaseFileCode>0</EthicsPoint_CaseFileCode>
            <FileURL/>
            <ReporterFile>0</ReporterFile>
        </Attachments>
        <Attachments>
            <CaseCode>6789</CaseCode>
            <FileCode>171</FileCode>
            <RestitutionCode>0</RestitutionCode>
            <FileType>image/jpeg</FileType>
            <DateUploaded>2018-01-11T12:59:01.123</DateUploaded>
            <Key/>
            <FileName>171-Huge Fluffy Dog.jpg</FileName>
            <Size>70808</Size>
            <FileCategoryCode>Other</FileCategoryCode>
            <Original_Description/>
            <TranslationNeeded_Description>0</TranslationNeeded_Description>
            <EthicsPoint_CaseFileCode>0</EthicsPoint_CaseFileCode>
            <FileURL/>
            <ReporterFile>0</ReporterFile>
        </Attachments>
        <Attachments>
            <CaseCode>6789</CaseCode>
            <FileCode>172</FileCode>
            <RestitutionCode>0</RestitutionCode>
            <FileType>application/vnd.openxmlformats-officedocument.wordprocessingml.document</FileType>
            <DateUploaded>2018-01-11T12:59:06.213</DateUploaded>
            <Key/>
            <FileName>172-Test file.docx</FileName>
            <Size>12628</Size>
            <FileCategoryCode>Other</FileCategoryCode>
            <Original_Description/>
            <TranslationNeeded_Description>0</TranslationNeeded_Description>
            <EthicsPoint_CaseFileCode>0</EthicsPoint_CaseFileCode>
            <FileURL/>
            <ReporterFile>0</ReporterFile>
        </Attachments>
        <Attachments>
            <CaseCode>6789</CaseCode>
            <FileCode>173</FileCode>
            <RestitutionCode>0</RestitutionCode>
            <FileType>application/pdf</FileType>
            <DateUploaded>2018-01-11T12:59:11.297</DateUploaded>
            <Key/>
            <FileName>173-The Raven.pdf</FileName>
            <Size>446126</Size>
            <FileCategoryCode>Other</FileCategoryCode>
            <Original_Description/>
            <TranslationNeeded_Description>0</TranslationNeeded_Description>
            <EthicsPoint_CaseFileCode>0</EthicsPoint_CaseFileCode>
            <FileURL/>
            <ReporterFile>0</ReporterFile>
        </Attachments>
        <CustomFields>
            <CaseCode>6789</CaseCode>
            <CustomFieldName>Interpreter Used</CustomFieldName>
            <CustomFieldValue>No</CustomFieldValue>
            <DisplaySection>Case</DisplaySection>
            <FieldType>Dropdown</FieldType>
        </CustomFields>
    </Case>
</Cases>

I'm using the following XSD: PasteBin

As far as I can tell there are no multiple CaseCodes inside a Case. I'm confused, don't work with XML much, any help or pointers are much appriciated.

CodePudding user response:

I tried to validate the XML against the XSD.

The validation revealed few errors.

You need to make a decision about what is correct XML or XSD.

[Error] Canolyb1.xml:6:13: cvc-complex-type.2.4.a: Invalid content was found starting with element 'TierName'. One of '{EPRSNo}' is expected.
[Error] Canolyb1.xml:57:15: cvc-complex-type.2.4.a: Invalid content was found starting with element 'FirstName'. One of '{NamePrefix}' is expected.
[Error] Canolyb1.xml:153:21: cvc-complex-type.2.4.a: Invalid content was found starting with element 'RestitutionCode'. One of '{DataCode}' is expected.
[Error] Canolyb1.xml:169:21: cvc-complex-type.2.4.a: Invalid content was found starting with element 'RestitutionCode'. One of '{DataCode}' is expected.
[Error] Canolyb1.xml:185:21: cvc-complex-type.2.4.a: Invalid content was found starting with element 'RestitutionCode'. One of '{DataCode}' is expected.
[Error] Canolyb1.xml:201:21: cvc-complex-type.2.4.a: Invalid content was found starting with element 'RestitutionCode'. One of '{DataCode}' is expected.
  • Related