使用 SSIS 和 XSD 文件导入 8GB XML 文件

Question

>我正在尝试将所有数据导出到一个大型 8GB xml 文档中，该文档使用 SSIS 2017 将一组与之关联的 XSD 文件导出到 SQL Server 目标，但是我收到错误"XML源无法处理 XML 数据。不支持替换组">

这是一个常见的错误吗？ 我可以使用其他方法来提取数据吗？我已经尝试过在SSIS上">生成XSD"，但这也失败了。

以下是提供的主要 XSD 文件：

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

这是 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>

Answer 1

您得到的错误是 SSIS XML 加载不支持替换组。XSD 文件中的违规行大概是这样的：

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

您将不得不弄清楚是否可以删除此行。(从您的示例 XML 来看，您似乎不需要它，但当然可以。

有关详细信息，请参阅此处，了解为什么你可能找不到对替换组的太多支持。您也可以谷歌错误并找到更多信息。