Phil Baines
Phil Baines

Reputation: 467

Import 8GB XML file using SSIS and XSD file

I am trying to export all the data in a large 8GB xml document which has a set of XSD files associated with it using SSIS 2017 to a SQL Server destination, however I get the error "The XML Source was unable to process the XML data. Substitution Groups not supported"

Is this a common error? are there other methods I can use to extract the data? I have tried "Generate XSD" on SSIS but that fails too.

Below is the main XSD file provided:

<xs:schema xmlns:xal="urn:oasis:names:tc:ciq:xal:3" xmlns:core="http://mbie.govt.nz/nzbn/core/2" xmlns:coy="http://business.govt.nz/companies/1"
    xmlns:xnl="urn:oasis:names:tc:ciq:xnl:3" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"
    targetNamespace="http://business.govt.nz/companies/1" xmlns:pil="urn:oasis:names:tc:ciq:xpil:3" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:vc="http://www.w3.org/2007/XMLSchema-versioning" vc:minVersion="1.1">
    <xs:import namespace="http://mbie.govt.nz/nzbn/core/2" schemaLocation="nzbn-core-v2.xsd" />
    <xs:import namespace="urn:oasis:names:tc:ciq:xal:3" schemaLocation="ciq/xAL.xsd" />
    <xs:import namespace="urn:oasis:names:tc:ciq:xnl:3" schemaLocation="ciq/xNL.xsd" />
    <xs:import namespace="urn:oasis:names:tc:ciq:xpil:3" schemaLocation="ciq/xPILNZProfile.xsd" />

    <xs:element name="CompanyDirector" type="coy:CompanyDirector" substitutionGroup="core:Director" />

    <xs:complexType name="Company">
        <xs:complexContent>
            <xs:extension base="core:BusinessEntity">
                <xs:sequence>
                    <xs:element name="AnnualReturnFilingMonth" type="xs:int" minOccurs="0" maxOccurs="1" />
                    <xs:element name="AnnualReturnLastFiled" type="xs:dateTime" minOccurs="0" maxOccurs="1" />
                    <xs:element name="FinancialReportingFilingMonth" type="xs:int" minOccurs="0"
                        maxOccurs="1" />
                    <xs:element name="HasConstitutionFiled" type="xs:boolean" minOccurs="0" maxOccurs="1" />
                    <xs:element name="CountryOfOrigin" type="xs:string" minOccurs="0" maxOccurs="1" />
                    <!-- TODO Confirm type -->
                    <xs:element name="PreviousCompanyStatus" type="coy:PreviousCompanyStatus" minOccurs="0"
                        maxOccurs="unbounded" />
                    <xs:element name="PreviousCompanyName" type="coy:PreviousCompanyName" minOccurs="0"
                        maxOccurs="unbounded" />
                    <xs:element name="BusinessIndustryClassificationCode" type="xs:NCName" minOccurs="0"
                        maxOccurs="1" />
                    <xs:element name="ShareRegisterAddress" type="coy:ShareRegisterAddress" minOccurs="0"
                        maxOccurs="unbounded" />
                    <xs:element name="AddressForRecords" type="coy:AddressForRecords" minOccurs="0" 
                        maxOccurs="unbounded" />
                    <xs:element name="HistoricAddress" type="xal:AddressType" minOccurs="0" maxOccurs="unbounded" />
                    <xs:element name="Shareholding" type="coy:Shareholding" minOccurs="0" maxOccurs="1" />
                    <xs:element name="UltimateHoldingCompany" type="coy:UltimateHoldingCompany" minOccurs="0" maxOccurs="1" />
                    <xs:element name="Document" type="coy:Document" minOccurs="0" maxOccurs="unbounded" />
                    <xs:element name="InsolvencyDetails" type="coy:InsolvencyDetails" minOccurs="0" maxOccurs="unbounded"/>
                    <xs:element name="PersonAuthorisedForService" type="coy:PersonAuthorisedForService" minOccurs="0" maxOccurs="unbounded"/>
                </xs:sequence>
            </xs:extension>
        </xs:complexContent>
    </xs:complexType>

    <xs:complexType name="PreviousCompanyStatus">
        <xs:simpleContent>
            <xs:extension base="xs:string">
                <xs:attributeGroup ref="core:grValidityDate" />
            </xs:extension>
        </xs:simpleContent>
    </xs:complexType>

    <xs:complexType name="PreviousCompanyName">
        <xs:simpleContent>
            <xs:extension base="xs:string">
                <xs:attributeGroup ref="core:grValidityDate" />
            </xs:extension>
        </xs:simpleContent>
    </xs:complexType>

    <!-- Standard CIQ xal:Address elements are re-used for the extended data schema. The type of the address is determined 
        by the containing element. This works around the limitation of the valid CIQ address types not matching the address types 
        available in the Companies Register. -->
    <xs:complexType name="ShareRegisterAddress">
        <xs:complexContent>
            <xs:extension base="xal:AddressType" />
        </xs:complexContent>
    </xs:complexType>

    <xs:complexType name="AddressForRecords">
        <xs:complexContent>
            <xs:extension base="xal:AddressType" />
        </xs:complexContent>
    </xs:complexType>

    <xs:complexType name="UltimateHoldingCompany">
        <xs:sequence>
            <xs:element ref="xnl:OrganisationName" minOccurs="0" maxOccurs="1" />
            <xs:element ref="pil:Identifiers" minOccurs="0" maxOccurs="1" />
            <xs:element name="CountryOfOrigin" type="xs:string" minOccurs="0" maxOccurs="1"/>
            <xs:element name="Address" type="xal:AddressType" minOccurs="0" maxOccurs="1"/>
        </xs:sequence>
        <xs:attribute name="Type" type="xs:string"/>
        <xs:attributeGroup ref="core:grPartyID"/>
        <xs:attribute name="DateValidFrom" type="xs:dateTime"/>
    </xs:complexType>

    <!-- Added Director type to support fields that don't map cleanly to CIQ. -->
    <xs:complexType name="CompanyDirector">
        <xs:complexContent>
            <xs:extension base="core:Director">
                <xs:sequence>
                    <!-- Appointment and Ceased dates now DateValidFrom and DateValidTo -->
                    <xs:element name="Status" type="coy:DirectorStatus" minOccurs="0" maxOccurs="1"/>
                    <xs:element name="Asic" type="coy:Asic" minOccurs="0" maxOccurs="1" />
                </xs:sequence>
            </xs:extension>
        </xs:complexContent>
    </xs:complexType>

    <xs:simpleType name="DirectorStatus">
        <xs:restriction base="xs:normalizedString">
            <xs:enumeration value="Active"/>
            <xs:enumeration value="Inactive"/>
            <xs:enumeration value="Pending"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:complexType name="Asic">
        <xs:sequence>
            <!-- Identifier of Type=CommonUse, OrganisationID=ASIC -->
            <xs:element ref="pil:Identifiers" minOccurs="0" maxOccurs="1" />
            <!-- Name of the Australian company -->
            <xs:element ref="xnl:OrganisationName" minOccurs="0" maxOccurs="1" />
            <!-- Address of the Australian company -->
            <xs:element name="RegisteredOfficeAddress" type="xal:AddressType" minOccurs="0" maxOccurs="1" />
        </xs:sequence>
    </xs:complexType>

    <xs:complexType name="Shareholder">
        <!-- Shareholders can be a person or an organisation Companies Register returns the entire entity data for a shareholder 
            entity. -->
        <xs:sequence>
            <xs:choice>
                <xs:sequence>
                    <xs:element ref="xnl:OrganisationName" minOccurs="0" maxOccurs="1" />
                    <xs:element ref="pil:Identifiers" minOccurs="0" maxOccurs="1" />
                </xs:sequence>
                <xs:element ref="xnl:PersonName" />
            </xs:choice>
            <xs:element name="PhysicalAddress" type="xal:AddressType" minOccurs="0" maxOccurs="1" />
        </xs:sequence>
        <xs:attribute name="Status" use="optional">
            <xs:simpleType>
                <xs:restriction base="xs:normalizedString">
                    <xs:enumeration value="Current" />
                    <xs:enumeration value="Inactive" />
                </xs:restriction>
            </xs:simpleType>
        </xs:attribute>
        <xs:attribute name="Type" type="coy:ShareholderType" use="optional" />
        <xs:attributeGroup ref="core:grPartyID" />
    </xs:complexType>

    <xs:simpleType name="ShareholderType">
        <xs:restriction base="xs:normalizedString">
            <xs:enumeration value="IndividualRole" />
            <xs:enumeration value="EntityOrganistionRole" />
        </xs:restriction>
    </xs:simpleType>

    <xs:complexType name="HistoricShareholder">
        <!-- Shareholders can be a person or an organisation Companies Register returns the entire entity data for a shareholder 
            entity. -->
        <xs:complexContent>
            <xs:extension base="coy:Shareholder">
                <xs:attribute name="DateValidTo" type="xs:dateTime" use="optional"/>
            </xs:extension>
        </xs:complexContent>
    </xs:complexType>

    <xs:complexType name="ShareAllocation">
        <xs:sequence>
            <xs:element name="Allocation" type="xs:integer" minOccurs="0" maxOccurs="1" />
            <!-- TODO Presumably a share allocation must have at least one current shareholder? -->
            <xs:element name="Shareholder" type="coy:Shareholder" minOccurs="0" maxOccurs="unbounded" />
        </xs:sequence>
        <xs:attributeGroup ref="core:grPartyID" />
    </xs:complexType>

    <xs:complexType name="Shareholding">
        <xs:sequence>
            <xs:element name="ExtensiveShareholding" type="xs:boolean" minOccurs="0" maxOccurs="1" />
            <xs:element name="TotalNumberOfShares" type="xs:integer" minOccurs="0" maxOccurs="1" />
            <xs:element name="ShareAllocation" type="coy:ShareAllocation" minOccurs="0" maxOccurs="unbounded" />
            <xs:element name="HistoricShareholder" type="coy:HistoricShareholder" minOccurs="0" maxOccurs="unbounded" />
        </xs:sequence>
    </xs:complexType>

    <xs:complexType name="Document">
        <xs:attribute name="Archived" type="xs:boolean" use="optional" />
        <!-- This could be an enumeration -->
        <xs:attribute name="FilingCode" type="xs:string" use="optional" />
        <xs:attribute name="FilingCodeDescription" type="xs:string" use="optional" />
        <xs:attribute name="RegisteredDate" type="xs:dateTime" use="optional" />
    </xs:complexType>

    <xs:complexType name="InsolvencyDetails">
        <xs:annotation>
            <xs:documentation>Optional insolvency details</xs:documentation>
        </xs:annotation>
        <xs:sequence>
            <xs:element name="Appointee" type="coy:Appointee" minOccurs="0" maxOccurs="unbounded" />
            <xs:element name="Report" type="coy:InsolvencyReport" minOccurs="0" maxOccurs="unbounded" />
        </xs:sequence>
        <xs:attribute name="Commenced" type="xs:dateTime" />
    </xs:complexType>

    <xs:complexType name="Appointee">
        <xs:sequence>
            <xs:element ref="xnl:PersonName" minOccurs="0" maxOccurs="1" />
            <xs:element ref="xnl:OrganisationName" minOccurs="0" maxOccurs="1" />
            <xs:element ref="pil:ContactNumbers" minOccurs="0" maxOccurs="1" />
            <xs:element ref="pil:ElectronicAddressIdentifiers" minOccurs="0" maxOccurs="1"/>
            <xs:element name="PhysicalAddress" type="xal:AddressType" minOccurs="0" maxOccurs="1" />
        </xs:sequence>
        <xs:attributeGroup ref="core:grValidityDate" />
    </xs:complexType>

    <xs:complexType name="InsolvencyReport">
        <xs:attribute name="Name" type="xs:string" use="optional"/>
        <xs:attribute name="Filed" type="xs:boolean" use="optional"/>
        <!-- TODO This is a bit of a vague name -->
        <xs:attribute name="Date" type="xs:date" use="optional"/>
    </xs:complexType>

    <xs:complexType name="PersonAuthorisedForService">
        <xs:sequence>
            <xs:element ref="xnl:PersonName" minOccurs="0" maxOccurs="1" />
            <xs:element name="Address" type="xal:AddressType" minOccurs="0" maxOccurs="1"/>
        </xs:sequence>
        <xs:attributeGroup ref="core:grValidityDate"/>
    </xs:complexType>
</xs:schema>

This is a sample of the XML:

<?xml version="1.0" ?>
<N8:EntityList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://mbie.govt.nz/nzbn/core/2/support business-entity-list-v2.xsd http://business.govt.nz/companies/1 Companies.xsd" xmlns:N8="http://mbie.govt.nz/nzbn/core/2/support" xmlns:N3="urn:oasis:names:tc:ciq:ct:3" xmlns:N2="urn:oasis:names:tc:ciq:xnl:3" xmlns:N4="http://www.w3.org/1999/xlink" xmlns:N7="urn:oasis:names:tc:ciq:xprl:3" xmlns:N5="urn:oasis:names:tc:ciq:xpil:3" xmlns:N6="urn:oasis:names:tc:ciq:xal:3" xmlns:N1="http://mbie.govt.nz/nzbn/core/2" xmlns:N9="http://business.govt.nz/companies/1">
  <N8:Entity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:N8="http://mbie.govt.nz/nzbn/core/2/support" xmlns="" xsi:type="N9:Company" PartyID="9429046940575" PartyIDType="NZBN">
    <N2:OrganisationName>
      <N2:NameElement N2:ElementType="FullName">RELEASE WOF TEST 01082018 LIMITED</N2:NameElement>
    </N2:OrganisationName>
    <N5:Addresses>
      <N5:Address N6:Type="NZUnstructured" N6:AddressID="580631" N6:AddressIDType="PAFID" N6:Usage="Contact" N3:DateValidFrom="2018-08-01T00:00:00.000+12:00">
        <N6:FreeTextAddress>
          <N6:AddressLine N6:Type="Address line 1">135 Mount Albert Road</N6:AddressLine>
          <N6:AddressLine N6:Type="Address line 2">Mount Albert</N6:AddressLine>
          <N6:AddressLine N6:Type="Address line 3">test city</N6:AddressLine>
        </N6:FreeTextAddress>
        <N6:Country>
          <N6:NameElement N6:NameCode="NZ" N3:Abbreviation="false">Country</N6:NameElement>
        </N6:Country>
        <N6:PostCode>
          <N6:Identifier N6:Type="NZPostCode">1025</N6:Identifier>
        </N6:PostCode>
      </N5:Address>
      <N5:Address N6:Type="NZUnstructured" N6:AddressID="580631" N6:AddressIDType="PAFID" N6:Usage="Billing" N3:DateValidFrom="2018-08-01T00:00:00.000+12:00">
        <N6:FreeTextAddress>
          <N6:AddressLine N6:Type="Address line 1">135 Mount Albert Road</N6:AddressLine>
          <N6:AddressLine N6:Type="Address line 2">Mount Albert</N6:AddressLine>
          <N6:AddressLine N6:Type="Address line 3">test city</N6:AddressLine>
        </N6:FreeTextAddress>
        <N6:Country>
          <N6:NameElement N6:NameCode="NZ" N3:Abbreviation="false">Country</N6:NameElement>
        </N6:Country>
        <N6:PostCode>
          <N6:Identifier N6:Type="NZPostCode">1111</N6:Identifier>
        </N6:PostCode>
      </N5:Address>
    </N5:Addresses>
    <N5:Events>
      <N5:Event N5:Type="Date of registration">2018-08-01 22:16:43</N5:Event>
    </N5:Events>
    <N5:Identifiers>
      <N5:Identifier N5:Type="CompanyID">
        <N5:IdentifierElement>12313123</N5:IdentifierElement>
        <N5:IssuerName>
          <N2:NameElement>Companies Register</N2:NameElement>
        </N5:IssuerName>
      </N5:Identifier>
    </N5:Identifiers>
    <N5:OrganisationInfo N5:Type="LTD" N5:Status="Registered"></N5:OrganisationInfo>
    <N1:Director xsi:type="N9:CompanyDirector" DateValidFrom="2018-08-01T00:00:00.000+12:00">
      <N2:PersonName N2:Type="OfficialName">
        <N2:NameElement N2:ElementType="FirstName">New</N2:NameElement>
        <N2:NameElement N2:ElementType="LastName">DIRECTOR</N2:NameElement>
      </N2:PersonName>
      <N9:Status>Active</N9:Status>
    </N1:Director>
    <N1:Director xsi:type="N9:CompanyDirector" DateValidFrom="2018-08-01T00:00:00.000+12:00">
      <N2:PersonName N2:Type="OfficialName">
        <N2:NameElement N2:ElementType="FirstName">Current</N2:NameElement>
        <N2:NameElement N2:ElementType="LastName">DIRECTOR</N2:NameElement>
      </N2:PersonName>
      <N9:Status>Active</N9:Status>
    </N1:Director>
    <N9:AnnualReturnFilingMonth>3</N9:AnnualReturnFilingMonth>
    <N9:HasConstitutionFiled>false</N9:HasConstitutionFiled>
    <N9:PreviousCompanyStatus DateValidFrom="2018-08-01T00:00:00.000+12:00">Registered</N9:PreviousCompanyStatus>
    <N9:Shareholding>
      <N9:ExtensiveShareholding>false</N9:ExtensiveShareholding>
      <N9:TotalNumberOfShares>100</N9:TotalNumberOfShares>
      <N9:ShareAllocation>
        <N9:Allocation>100</N9:Allocation>
        <N9:Shareholder Type="IndividualRole">
          <N2:PersonName>
            <N2:NameElement N2:ElementType="FirstName">Current</N2:NameElement>
            <N2:NameElement N2:ElementType="LastName">DIRECTOR</N2:NameElement>
          </N2:PersonName>
        </N9:Shareholder>
      </N9:ShareAllocation>
    </N9:Shareholding>
  </N8:Entity>
</N8:EntityList>

Upvotes: 2

Views: 279

Answers (1)

BobRodes
BobRodes

Reputation: 6165

The error that you are getting is that substitution groups are not supported by the SSIS XML load. The offending line in your XSD file is presumably this:

 <xs:element name="CompanyDirector" type="coy:CompanyDirector" substitutionGroup="core:Director" />

You're going to have to figure out whether you can delete this line. (From your sample XML, it doesn't look like you need it, but of course you may.)

For more info, see this on why you might not find much support for substitution groups. You can also google the error and find more information.

Upvotes: 1

Related Questions