I am Importing an XML file into Sql Server by using SqlBulkCopy in c#. I have done it by implimenting IdataReader in a local class. XML Sample is as follows
<CSDSupplierBulkXMLFile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Header><DateTimeCreated>2017-04-19T00:39:40.600</DateTimeCreated><FileType>daily</FileType><MonthlyDate xsi:nil="true"/><DailyDate>2017-04-18</DailyDate><SupplierSourceSystem>CSD</SupplierSourceSystem><SupplierCount>500</SupplierCount></Header><Suppliers
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Supplier><SupplierIdentification><SupplierNumber>MAAA0020683</SupplierNumber><UniqueRegistrationReferenceNumber>E1C0E850-7C05-438F-92D6-D8E7280A4F98</UniqueRegistrationReferenceNumber><IsActive>true</IsActive><SupplierInactiveReason/><SupplierInactiveDate>1900-01-01T00:00:00</SupplierInactiveDate><IsAssociated>false</IsAssociated><SupplierTypeCode>01</SupplierTypeCode><SupplierTypeName>Individual</SupplierTypeName><SupplierSubTypeCode>60</SupplierSubTypeCode><SupplierSubTypeName>Individual</SupplierSubTypeName><GovernmentTypeCode>60</GovernmentTypeCode><GovernmentTypeName>Individual</GovernmentTypeName><IndustryClassificationCode>13</IndustryClassificationCode><IndustryClassificationName>Professional,
scientific and technical activities</IndustryClassificationName><CountryOfOriginCode>ZAF</CountryOfOriginCode><CountryOfOriginName>South Africa</CountryOfOriginName><LegalName>Marlene Bossett</LegalName><TradingName/><IDTypeCode>001</IDTypeCode><IDTypeName>South
African Identification Number</IDTypeName><SAIDNumber>6204110229087</SAIDNumber><ForeignIDNumber/><ForeignPassportNumber/><WorkPermitNumber/><SACompanyNumber/><RegistrationDate>1900-01-01 12:00:00 AM</RegistrationDate><ForeignCompanyRegistrationNumber/><SATrustRegistrationNumber/><ForeignTrustRegistrationNumber/><DateOperationsStarted
xsi:nil="true"/><HaveBankAccount>true</HaveBankAccount><BusinessStatusCode>0</BusinessStatusCode><BusinessStatusName>No Status</BusinessStatusName><BusinessStatusLastVerificationDate>1900-01-01T00:00:00</BusinessStatusLastVerificationDate><CreatedDate>2016-01-18T20:15:06</CreatedDate><EditDate>2017-04-18T18:49:19.967</EditDate></SupplierIdentification><SupplierFlags
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlag><SupplierFlagType>Restricted</SupplierFlagType><SupplierFlagDescription>This is a Restricted Supplier/Tender Defaulter</SupplierFlagDescription><SupplierFlagValue>false</SupplierFlagValue><SupplierFlagLastVerificationDate>2017-04-18T18:49:19.967</SupplierFlagLastVerificationDate><SupplierFlagDetails
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlagDetail/></SupplierFlagDetails></SupplierFlag><SupplierFlag><SupplierFlagType>DPSA</SupplierFlagType><SupplierFlagDescription>This is a Government Employee</SupplierFlagDescription><SupplierFlagValue>false</SupplierFlagValue><SupplierFlagLastVerificationDate>2017-04-18T18:49:19.967</SupplierFlagLastVerificationDate><SupplierFlagDetails
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlagDetail></SupplierFlagDetail></SupplierFlagDetails></SupplierFlag></SupplierFlags><Contacts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Contact><ContactID>28467</ContactID><ContactTypeCode>03</ContactTypeCode><ContactTypeName>Administration</ContactTypeName><IsPreferred>true</IsPreferred><Name>Marlene</Name><Surname>Bossett</Surname><IdentificationTypeCode>001</IdentificationTypeCode><IdentificationTypeName>South
African Identification Number</IdentificationTypeName><SAIDNumber>6204110229087</SAIDNumber><ForeignIDNumber/><ForeignPassportNumber/><WorkPermitNumber/><PreferCellphone>false</PreferCellphone><PreferEmail>true</PreferEmail><PreferFax>false</PreferFax><PreferPostal>false</PreferPostal><PreferSMS>false</PreferSMS><PreferTelephone>false</PreferTelephone><EmailAddress>marleneb@sonquasolutions.co.za</EmailAddress><CellphoneNumber>0837033980</CellphoneNumber><FaxNumber/><TelephoneNumber/><CSDUser>true</CSDUser><IsActive>true</IsActive><CreatedDate>2016-01-17T16:49:43</CreatedDate><EditDate>2016-01-17T17:19:46</EditDate></Contact></Contacts><Addresses
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Address><AddressID>35058</AddressID><IsPreferred>true</IsPreferred><AddressTypeCode>02</AddressTypeCode><AddressTypeName>Postal </AddressTypeName><AddressLine1>P.O Box 18</AddressLine1><AddressLine2>Bergvliet</AddressLine2><CountryCode>ZAF</CountryCode><CountryName>South
Africa</CountryName><ProvinceCode>1</ProvinceCode><ProvinceName>Western Cape</ProvinceName><DistrictCode>199</DistrictCode><DistrictName>City of Cape Town</DistrictName><MunicipalityCode>199</MunicipalityCode><MunicipalityName>City of Cape Town</MunicipalityName><CityCode>199002</CityCode><CityName>Cape
Metro</CityName><SuburbCode>199002003</SuburbCode><SuburbName>Cape Metro NU1</SuburbName><WardCode xsi:nil="true"/><WardName xsi:nil="true"/><PostalCode>7864</PostalCode><IsPostalAddress>false</IsPostalAddress><IsDeliveryAddress>false</IsDeliveryAddress><IsPhysicalAddress>false</IsPhysicalAddress><IsPaymentAddress>true</IsPaymentAddress><CreatedDate>2016-01-18T20:15:06.923</CreatedDate><EditDate>2016-01-18T20:15:06.923</EditDate></Address><Address><AddressID>35059</AddressID><IsPreferred>false</IsPreferred><AddressTypeCode>01</AddressTypeCode><AddressTypeName>Physical
</AddressTypeName><AddressLine1>4 Eleventh Road</AddressLine1><AddressLine2>Heathfield</AddressLine2><CountryCode>ZAF</CountryCode><CountryName>South Africa</CountryName><ProvinceCode>1</ProvinceCode><ProvinceName>Western Cape</ProvinceName><DistrictCode>199</DistrictCode><DistrictName>City
of Cape Town</DistrictName><MunicipalityCode>199</MunicipalityCode><MunicipalityName>City of Cape Town</MunicipalityName><CityCode>199041</CityCode><CityName>Cape Town</CityName><SuburbCode>199041099</SuburbCode><SuburbName>Heathfield</SuburbName><WardCode>19100072002754</WardCode><WardName>72</WardName><PostalCode>7945</PostalCode><IsPostalAddress>false</IsPostalAddress><IsDeliveryAddress>true</IsDeliveryAddress><IsPhysicalAddress>false</IsPhysicalAddress><IsPaymentAddress>false</IsPaymentAddress><CreatedDate>2016-01-18T20:15:06.970</CreatedDate><EditDate>2016-01-18T20:15:06.970</EditDate></Address></Addresses><BankAccounts
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><BankAccount><IsPreferredAccount>true</IsPreferredAccount><BankAccountID>25013</BankAccountID><AccountHolder xsi:nil="true"/><BankAccountTypeCode xsi:nil="true"/><BankAccountTypeName xsi:nil="true"/><BankName
xsi:nil="true"/><BankCode xsi:nil="true"/><BranchName xsi:nil="true"/><BranchNumber xsi:nil="true"/><AccountNumber xsi:nil="true"/><IsForeignBankAccount xsi:nil="true"/><BankAccountStatusCode>02</BankAccountStatusCode><BankAccountStatusName>Verification Failed</BankAccountStatusName><AddressLine1
xsi:nil="true"/><AddressLine2 xsi:nil="true"/><CountryCode xsi:nil="true"/><CountryName xsi:nil="true"/><ZipCode xsi:nil="true"/><CreatedDate xsi:nil="true"/><EditDate xsi:nil="true"/></BankAccount></BankAccounts><SupplierDetailsTax xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><IncomeTaxNumber>0964141022</IncomeTaxNumber><IsRegistered>true</IsRegistered><LastVerificationDate>2017-04-18T18:49:00</LastVerificationDate><PAYENumber/><TaxClearanceCertificateExpiryDate>2018-04-18</TaxClearanceCertificateExpiryDate><VATNumber/><IsValidCertificate>true</IsValidCertificate><ValidationResponse>Tax
Compliant</ValidationResponse><CreatedDate>2016-01-17T17:43:53</CreatedDate><EditDate>2017-04-18T18:49:19.980</EditDate></SupplierDetailsTax><Directors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Director><DirectorID>41192</DirectorID><Name>Marlene</Name><Surname>Bossett</Surname><AppointmentDate>2016-01-17T00:00:00</AppointmentDate><IsActive>true</IsActive><CountryTypeCode>ZAF</CountryTypeCode><CountryTypeName>South
Africa</CountryTypeName><IDTypeCode>001</IDTypeCode><IDTypeName>South African Identification Number</IDTypeName><DirectorStatusTypeCode>A</DirectorStatusTypeCode><DirectorStatusTypeName>Active</DirectorStatusTypeName><SAIDNumber>6204110229087</SAIDNumber><ForeignIDNumber/><ForeignPassportNumber/><WorkPermitNumber/><LastVerificationDate>1900-01-01T00:00:00</LastVerificationDate><DirectorTypes
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><DirectorType><DirectorTypeCode>Z</DirectorTypeCode><DirectorTypeName>Founding Member</DirectorTypeName></DirectorType></DirectorTypes><DirectorFlags xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><DirectorFlag><SupplierFlagType>DPSA</SupplierFlagType><SupplierFlagDescription>This
is a Government Employee</SupplierFlagDescription><SupplierFlagValue>false</SupplierFlagValue><SupplierFlagLastVerificationDate>2017-04-18T18:49:19.997</SupplierFlagLastVerificationDate><SupplierFlagDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlagDetail></SupplierFlagDetail></SupplierFlagDetails></DirectorFlag><DirectorFlag><SupplierFlagType>Restricted</SupplierFlagType><SupplierFlagDescription>This
is a Restricted Supplier/Tender Defaulter</SupplierFlagDescription><SupplierFlagValue>false</SupplierFlagValue><SupplierFlagLastVerificationDate>2017-04-18T18:49:19.980</SupplierFlagLastVerificationDate><SupplierFlagDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlagDetail/></SupplierFlagDetails></DirectorFlag></DirectorFlags><CreatedDate>2016-01-17T17:48:24</CreatedDate><EditDate>2016-01-17T17:48:24</EditDate></Director></Directors><SupplierCommodityDetails
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierCommodityList><SupplierCommodity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><CommodityGroupID>40766</CommodityGroupID><Name>Human Resources</Name><Description>Including Skills Development,
Research, Transformation, Change Management and Performance Management</Description><NationWide>true</NationWide><ProvinceWide>false</ProvinceWide><IsActive>true</IsActive><SupplierCommodityItems xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierCommodityItem><CommodityCode>80111501</CommodityCode><CommodityName>Management
development</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative
Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111507</CommodityCode><CommodityName>Outplacement services</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human
resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111504</CommodityCode><CommodityName>Labor
training or development</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and
Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111701</CommodityCode><CommodityName>Staff recruiting services</CommodityName><ClassName>Personnel recruitment</ClassName><ClassCode>80111700</ClassCode><FamilyName>Human
resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111505</CommodityCode><CommodityName>Human
resources productivity audits</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals
and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111703</CommodityCode><CommodityName>Resume or curriculum vitae screening services</CommodityName><ClassName>Personnel
recruitment</ClassName><ClassCode>80111700</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111506</CommodityCode><CommodityName>Personnel
relocation</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative
Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem></SupplierCommodityItems><CreatedDate>2016-01-18T19:17:33</CreatedDate><EditDate>2016-11-09T15:38:29.453</EditDate></SupplierCommodity><SupplierCommodity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><CommodityGroupID>40767</CommodityGroupID><Name>Management
Consulting</Name><Description>OD, Change Management, Organisational Culture, Employment Equity, Audits, Impact Analyses, Policy and Strategy Development</Description><NationWide>true</NationWide><ProvinceWide>false</ProvinceWide><IsActive>true</IsActive><SupplierCommodityItems
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierCommodityItem><CommodityCode>80101506</CommodityCode><CommodityName>Organizational structure consultation</CommodityName><ClassName>Business and corporate management consultation services</ClassName><ClassCode>80101500</ClassCode><FamilyName>Management
advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80101508</CommodityCode><CommodityName>Business
intelligence consulting services</CommodityName><ClassName>Business and corporate management consultation services</ClassName><ClassCode>80101500</ClassCode><FamilyName>Management advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management
and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80101505</CommodityCode><CommodityName>Corporate objectives or policy development</CommodityName><ClassName>Business
and corporate management consultation services</ClassName><ClassCode>80101500</ClassCode><FamilyName>Management advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80101504</CommodityCode><CommodityName>Strategic
planning consultation services</CommodityName><ClassName>Business and corporate management consultation services</ClassName><ClassCode>80101500</ClassCode><FamilyName>Management advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management
and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80101702</CommodityCode><CommodityName>Productivity or efficiency studies or implementation</CommodityName><ClassName>Industrial
management</ClassName><ClassCode>80101700</ClassCode><FamilyName>Management advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem></SupplierCommodityItems><CreatedDate>2016-01-18T19:33:11</CreatedDate><EditDate>2016-01-18T20:01:35</EditDate></SupplierCommodity><SupplierCommodity
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><CommodityGroupID>40768</CommodityGroupID><Name>Training and Development</Name><Description>Including all leadership, diversity and soft skills training. Also soft skills training in schools, e.g., sexual
health, pregnancy, diversity, bullying, etc.</Description><NationWide>true</NationWide><ProvinceWide>false</ProvinceWide><IsActive>true</IsActive><SupplierCommodityItems xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierCommodityItem><CommodityCode>86101707</CommodityCode><CommodityName>Personal
care vocational training services</CommodityName><ClassName>Non scientific vocational training services</ClassName><ClassCode>86101700</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training
Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101804</CommodityCode><CommodityName>Commercial sector manpower development</CommodityName><ClassName>In service training and manpower
development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101710</CommodityCode><CommodityName>Teacher
training services</CommodityName><ClassName>Non scientific vocational training services</ClassName><ClassCode>86101700</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101807</CommodityCode><CommodityName>Management
sector manpower development</CommodityName><ClassName>In service training and manpower development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101705</CommodityCode><CommodityName>Clerical
training</CommodityName><ClassName>Non scientific vocational training services</ClassName><ClassCode>86101700</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101803</CommodityCode><CommodityName>Vocational
rehabilitation services</CommodityName><ClassName>In service training and manpower development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101701</CommodityCode><CommodityName>Communications
vocational training services</CommodityName><ClassName>Non scientific vocational training services</ClassName><ClassCode>86101700</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101802</CommodityCode><CommodityName>Re
training or refreshing training services</CommodityName><ClassName>In service training and manpower development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and
Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101808</CommodityCode><CommodityName>Public sector manpower development services</CommodityName><ClassName>In service training
and manpower development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem></SupplierCommodityItems><CreatedDate>2016-01-18T19:10:49</CreatedDate><EditDate>2016-01-18T19:39:19</EditDate></SupplierCommodity></SupplierCommodityList></SupplierCommodityDetails></Supplier></Suppliers></CSDSupplierBulkXMLFile>
Now there is an issue that xmlreader does not read second child <SupplierFlag> of parent node <SupplierFlags xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
Do I missing something.
It will be difficult to understand without what code you are writing for import. Would you post your code and specify line of code(s) where you see the issue?
नमस्ते,
[KaushaL] BlogTwitter [MS MVP 2008 & 2009] [MCC 2011] [MVP Reconnect 2017]
Don't forget to click "Mark as Answer" on the post that helped you
Now there is an issue that xmlreader does not read second child <SupplierFlag> of parent node <SupplierFlags xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
Do I missing something.
According to your code, I create a sample using the following code, it seems that everything works well, you could refer to it:
//create a memory stream with your xml content
//var stream = new MemoryStream(Encoding.UTF8.GetBytes(@"...your xml content..."));
var vColumns = new string[] { "vSupplierID", "vSupplierFlagType", "vSupplierFlagdescription", "vSupplierFlagValue", "vSupplierFlagLastVerificationDate", "vSupplierFlagDetail" };
var xmlReader = XmlReader.Create(stream);
var reader = new BulkFileReader(xmlReader, vColumns.Length, "SupplierFlag", false);
while (reader.Read())
{
for (var i = 0; i < vColumns.Length; i++)
{
Console.WriteLine("{0,-35}: {1}", vColumns[i], reader.GetValue(i));
}
Console.WriteLine();
}
The output as below:
Best regards,
Dillion
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
None
0 Points
8 Posts
Import XML Bulk File into SQL Server Using SqlBulkCopy in C#
Apr 21, 2017 01:28 PM|hajiahmadkhan|LINK
I am Importing an XML file into Sql Server by using SqlBulkCopy in c#. I have done it by implimenting IdataReader in a local class. XML Sample is as follows
<CSDSupplierBulkXMLFile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Header><DateTimeCreated>2017-04-19T00:39:40.600</DateTimeCreated><FileType>daily</FileType><MonthlyDate xsi:nil="true"/><DailyDate>2017-04-18</DailyDate><SupplierSourceSystem>CSD</SupplierSourceSystem><SupplierCount>500</SupplierCount></Header><Suppliers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Supplier><SupplierIdentification><SupplierNumber>MAAA0020683</SupplierNumber><UniqueRegistrationReferenceNumber>E1C0E850-7C05-438F-92D6-D8E7280A4F98</UniqueRegistrationReferenceNumber><IsActive>true</IsActive><SupplierInactiveReason/><SupplierInactiveDate>1900-01-01T00:00:00</SupplierInactiveDate><IsAssociated>false</IsAssociated><SupplierTypeCode>01</SupplierTypeCode><SupplierTypeName>Individual</SupplierTypeName><SupplierSubTypeCode>60</SupplierSubTypeCode><SupplierSubTypeName>Individual</SupplierSubTypeName><GovernmentTypeCode>60</GovernmentTypeCode><GovernmentTypeName>Individual</GovernmentTypeName><IndustryClassificationCode>13</IndustryClassificationCode><IndustryClassificationName>Professional, scientific and technical activities</IndustryClassificationName><CountryOfOriginCode>ZAF</CountryOfOriginCode><CountryOfOriginName>South Africa</CountryOfOriginName><LegalName>Marlene Bossett</LegalName><TradingName/><IDTypeCode>001</IDTypeCode><IDTypeName>South African Identification Number</IDTypeName><SAIDNumber>6204110229087</SAIDNumber><ForeignIDNumber/><ForeignPassportNumber/><WorkPermitNumber/><SACompanyNumber/><RegistrationDate>1900-01-01 12:00:00 AM</RegistrationDate><ForeignCompanyRegistrationNumber/><SATrustRegistrationNumber/><ForeignTrustRegistrationNumber/><DateOperationsStarted xsi:nil="true"/><HaveBankAccount>true</HaveBankAccount><BusinessStatusCode>0</BusinessStatusCode><BusinessStatusName>No Status</BusinessStatusName><BusinessStatusLastVerificationDate>1900-01-01T00:00:00</BusinessStatusLastVerificationDate><CreatedDate>2016-01-18T20:15:06</CreatedDate><EditDate>2017-04-18T18:49:19.967</EditDate></SupplierIdentification><SupplierFlags xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlag><SupplierFlagType>Restricted</SupplierFlagType><SupplierFlagDescription>This is a Restricted Supplier/Tender Defaulter</SupplierFlagDescription><SupplierFlagValue>false</SupplierFlagValue><SupplierFlagLastVerificationDate>2017-04-18T18:49:19.967</SupplierFlagLastVerificationDate><SupplierFlagDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlagDetail/></SupplierFlagDetails></SupplierFlag><SupplierFlag><SupplierFlagType>DPSA</SupplierFlagType><SupplierFlagDescription>This is a Government Employee</SupplierFlagDescription><SupplierFlagValue>false</SupplierFlagValue><SupplierFlagLastVerificationDate>2017-04-18T18:49:19.967</SupplierFlagLastVerificationDate><SupplierFlagDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlagDetail></SupplierFlagDetail></SupplierFlagDetails></SupplierFlag></SupplierFlags><Contacts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Contact><ContactID>28467</ContactID><ContactTypeCode>03</ContactTypeCode><ContactTypeName>Administration</ContactTypeName><IsPreferred>true</IsPreferred><Name>Marlene</Name><Surname>Bossett</Surname><IdentificationTypeCode>001</IdentificationTypeCode><IdentificationTypeName>South African Identification Number</IdentificationTypeName><SAIDNumber>6204110229087</SAIDNumber><ForeignIDNumber/><ForeignPassportNumber/><WorkPermitNumber/><PreferCellphone>false</PreferCellphone><PreferEmail>true</PreferEmail><PreferFax>false</PreferFax><PreferPostal>false</PreferPostal><PreferSMS>false</PreferSMS><PreferTelephone>false</PreferTelephone><EmailAddress>marleneb@sonquasolutions.co.za</EmailAddress><CellphoneNumber>0837033980</CellphoneNumber><FaxNumber/><TelephoneNumber/><CSDUser>true</CSDUser><IsActive>true</IsActive><CreatedDate>2016-01-17T16:49:43</CreatedDate><EditDate>2016-01-17T17:19:46</EditDate></Contact></Contacts><Addresses xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Address><AddressID>35058</AddressID><IsPreferred>true</IsPreferred><AddressTypeCode>02</AddressTypeCode><AddressTypeName>Postal </AddressTypeName><AddressLine1>P.O Box 18</AddressLine1><AddressLine2>Bergvliet</AddressLine2><CountryCode>ZAF</CountryCode><CountryName>South Africa</CountryName><ProvinceCode>1</ProvinceCode><ProvinceName>Western Cape</ProvinceName><DistrictCode>199</DistrictCode><DistrictName>City of Cape Town</DistrictName><MunicipalityCode>199</MunicipalityCode><MunicipalityName>City of Cape Town</MunicipalityName><CityCode>199002</CityCode><CityName>Cape Metro</CityName><SuburbCode>199002003</SuburbCode><SuburbName>Cape Metro NU1</SuburbName><WardCode xsi:nil="true"/><WardName xsi:nil="true"/><PostalCode>7864</PostalCode><IsPostalAddress>false</IsPostalAddress><IsDeliveryAddress>false</IsDeliveryAddress><IsPhysicalAddress>false</IsPhysicalAddress><IsPaymentAddress>true</IsPaymentAddress><CreatedDate>2016-01-18T20:15:06.923</CreatedDate><EditDate>2016-01-18T20:15:06.923</EditDate></Address><Address><AddressID>35059</AddressID><IsPreferred>false</IsPreferred><AddressTypeCode>01</AddressTypeCode><AddressTypeName>Physical </AddressTypeName><AddressLine1>4 Eleventh Road</AddressLine1><AddressLine2>Heathfield</AddressLine2><CountryCode>ZAF</CountryCode><CountryName>South Africa</CountryName><ProvinceCode>1</ProvinceCode><ProvinceName>Western Cape</ProvinceName><DistrictCode>199</DistrictCode><DistrictName>City of Cape Town</DistrictName><MunicipalityCode>199</MunicipalityCode><MunicipalityName>City of Cape Town</MunicipalityName><CityCode>199041</CityCode><CityName>Cape Town</CityName><SuburbCode>199041099</SuburbCode><SuburbName>Heathfield</SuburbName><WardCode>19100072002754</WardCode><WardName>72</WardName><PostalCode>7945</PostalCode><IsPostalAddress>false</IsPostalAddress><IsDeliveryAddress>true</IsDeliveryAddress><IsPhysicalAddress>false</IsPhysicalAddress><IsPaymentAddress>false</IsPaymentAddress><CreatedDate>2016-01-18T20:15:06.970</CreatedDate><EditDate>2016-01-18T20:15:06.970</EditDate></Address></Addresses><BankAccounts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><BankAccount><IsPreferredAccount>true</IsPreferredAccount><BankAccountID>25013</BankAccountID><AccountHolder xsi:nil="true"/><BankAccountTypeCode xsi:nil="true"/><BankAccountTypeName xsi:nil="true"/><BankName xsi:nil="true"/><BankCode xsi:nil="true"/><BranchName xsi:nil="true"/><BranchNumber xsi:nil="true"/><AccountNumber xsi:nil="true"/><IsForeignBankAccount xsi:nil="true"/><BankAccountStatusCode>02</BankAccountStatusCode><BankAccountStatusName>Verification Failed</BankAccountStatusName><AddressLine1 xsi:nil="true"/><AddressLine2 xsi:nil="true"/><CountryCode xsi:nil="true"/><CountryName xsi:nil="true"/><ZipCode xsi:nil="true"/><CreatedDate xsi:nil="true"/><EditDate xsi:nil="true"/></BankAccount></BankAccounts><SupplierDetailsTax xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><IncomeTaxNumber>0964141022</IncomeTaxNumber><IsRegistered>true</IsRegistered><LastVerificationDate>2017-04-18T18:49:00</LastVerificationDate><PAYENumber/><TaxClearanceCertificateExpiryDate>2018-04-18</TaxClearanceCertificateExpiryDate><VATNumber/><IsValidCertificate>true</IsValidCertificate><ValidationResponse>Tax Compliant</ValidationResponse><CreatedDate>2016-01-17T17:43:53</CreatedDate><EditDate>2017-04-18T18:49:19.980</EditDate></SupplierDetailsTax><Directors xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Director><DirectorID>41192</DirectorID><Name>Marlene</Name><Surname>Bossett</Surname><AppointmentDate>2016-01-17T00:00:00</AppointmentDate><IsActive>true</IsActive><CountryTypeCode>ZAF</CountryTypeCode><CountryTypeName>South Africa</CountryTypeName><IDTypeCode>001</IDTypeCode><IDTypeName>South African Identification Number</IDTypeName><DirectorStatusTypeCode>A</DirectorStatusTypeCode><DirectorStatusTypeName>Active</DirectorStatusTypeName><SAIDNumber>6204110229087</SAIDNumber><ForeignIDNumber/><ForeignPassportNumber/><WorkPermitNumber/><LastVerificationDate>1900-01-01T00:00:00</LastVerificationDate><DirectorTypes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><DirectorType><DirectorTypeCode>Z</DirectorTypeCode><DirectorTypeName>Founding Member</DirectorTypeName></DirectorType></DirectorTypes><DirectorFlags xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><DirectorFlag><SupplierFlagType>DPSA</SupplierFlagType><SupplierFlagDescription>This is a Government Employee</SupplierFlagDescription><SupplierFlagValue>false</SupplierFlagValue><SupplierFlagLastVerificationDate>2017-04-18T18:49:19.997</SupplierFlagLastVerificationDate><SupplierFlagDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlagDetail></SupplierFlagDetail></SupplierFlagDetails></DirectorFlag><DirectorFlag><SupplierFlagType>Restricted</SupplierFlagType><SupplierFlagDescription>This is a Restricted Supplier/Tender Defaulter</SupplierFlagDescription><SupplierFlagValue>false</SupplierFlagValue><SupplierFlagLastVerificationDate>2017-04-18T18:49:19.980</SupplierFlagLastVerificationDate><SupplierFlagDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierFlagDetail/></SupplierFlagDetails></DirectorFlag></DirectorFlags><CreatedDate>2016-01-17T17:48:24</CreatedDate><EditDate>2016-01-17T17:48:24</EditDate></Director></Directors><SupplierCommodityDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierCommodityList><SupplierCommodity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><CommodityGroupID>40766</CommodityGroupID><Name>Human Resources</Name><Description>Including Skills Development, Research, Transformation, Change Management and Performance Management</Description><NationWide>true</NationWide><ProvinceWide>false</ProvinceWide><IsActive>true</IsActive><SupplierCommodityItems xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierCommodityItem><CommodityCode>80111501</CommodityCode><CommodityName>Management development</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111507</CommodityCode><CommodityName>Outplacement services</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111504</CommodityCode><CommodityName>Labor training or development</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111701</CommodityCode><CommodityName>Staff recruiting services</CommodityName><ClassName>Personnel recruitment</ClassName><ClassCode>80111700</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111505</CommodityCode><CommodityName>Human resources productivity audits</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111703</CommodityCode><CommodityName>Resume or curriculum vitae screening services</CommodityName><ClassName>Personnel recruitment</ClassName><ClassCode>80111700</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80111506</CommodityCode><CommodityName>Personnel relocation</CommodityName><ClassName>Human resource development</ClassName><ClassCode>80111500</ClassCode><FamilyName>Human resources services</FamilyName><FamilyCode>80110000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem></SupplierCommodityItems><CreatedDate>2016-01-18T19:17:33</CreatedDate><EditDate>2016-11-09T15:38:29.453</EditDate></SupplierCommodity><SupplierCommodity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><CommodityGroupID>40767</CommodityGroupID><Name>Management Consulting</Name><Description>OD, Change Management, Organisational Culture, Employment Equity, Audits, Impact Analyses, Policy and Strategy Development</Description><NationWide>true</NationWide><ProvinceWide>false</ProvinceWide><IsActive>true</IsActive><SupplierCommodityItems xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierCommodityItem><CommodityCode>80101506</CommodityCode><CommodityName>Organizational structure consultation</CommodityName><ClassName>Business and corporate management consultation services</ClassName><ClassCode>80101500</ClassCode><FamilyName>Management advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80101508</CommodityCode><CommodityName>Business intelligence consulting services</CommodityName><ClassName>Business and corporate management consultation services</ClassName><ClassCode>80101500</ClassCode><FamilyName>Management advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80101505</CommodityCode><CommodityName>Corporate objectives or policy development</CommodityName><ClassName>Business and corporate management consultation services</ClassName><ClassCode>80101500</ClassCode><FamilyName>Management advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80101504</CommodityCode><CommodityName>Strategic planning consultation services</CommodityName><ClassName>Business and corporate management consultation services</ClassName><ClassCode>80101500</ClassCode><FamilyName>Management advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>80101702</CommodityCode><CommodityName>Productivity or efficiency studies or implementation</CommodityName><ClassName>Industrial management</ClassName><ClassCode>80101700</ClassCode><FamilyName>Management advisory services</FamilyName><FamilyCode>80100000</FamilyCode><SegmentName>Management and Business Professionals and Administrative Services</SegmentName><SegmentCode>80000000</SegmentCode></SupplierCommodityItem></SupplierCommodityItems><CreatedDate>2016-01-18T19:33:11</CreatedDate><EditDate>2016-01-18T20:01:35</EditDate></SupplierCommodity><SupplierCommodity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><CommodityGroupID>40768</CommodityGroupID><Name>Training and Development</Name><Description>Including all leadership, diversity and soft skills training. Also soft skills training in schools, e.g., sexual health, pregnancy, diversity, bullying, etc.</Description><NationWide>true</NationWide><ProvinceWide>false</ProvinceWide><IsActive>true</IsActive><SupplierCommodityItems xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SupplierCommodityItem><CommodityCode>86101707</CommodityCode><CommodityName>Personal care vocational training services</CommodityName><ClassName>Non scientific vocational training services</ClassName><ClassCode>86101700</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101804</CommodityCode><CommodityName>Commercial sector manpower development</CommodityName><ClassName>In service training and manpower development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101710</CommodityCode><CommodityName>Teacher training services</CommodityName><ClassName>Non scientific vocational training services</ClassName><ClassCode>86101700</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101807</CommodityCode><CommodityName>Management sector manpower development</CommodityName><ClassName>In service training and manpower development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101705</CommodityCode><CommodityName>Clerical training</CommodityName><ClassName>Non scientific vocational training services</ClassName><ClassCode>86101700</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101803</CommodityCode><CommodityName>Vocational rehabilitation services</CommodityName><ClassName>In service training and manpower development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101701</CommodityCode><CommodityName>Communications vocational training services</CommodityName><ClassName>Non scientific vocational training services</ClassName><ClassCode>86101700</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101802</CommodityCode><CommodityName>Re training or refreshing training services</CommodityName><ClassName>In service training and manpower development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem><SupplierCommodityItem><CommodityCode>86101808</CommodityCode><CommodityName>Public sector manpower development services</CommodityName><ClassName>In service training and manpower development</ClassName><ClassCode>86101800</ClassCode><FamilyName>Vocational training</FamilyName><FamilyCode>86100000</FamilyCode><SegmentName>Education and Training Services</SegmentName><SegmentCode>86000000</SegmentCode></SupplierCommodityItem></SupplierCommodityItems><CreatedDate>2016-01-18T19:10:49</CreatedDate><EditDate>2016-01-18T19:39:19</EditDate></SupplierCommodity></SupplierCommodityList></SupplierCommodityDetails></Supplier></Suppliers></CSDSupplierBulkXMLFile>
Now there is an issue that xmlreader does not read second child <SupplierFlag> of parent node <SupplierFlags xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
Do I missing something.
Thanks
All-Star
31332 Points
7048 Posts
Re: Import XML Bulk File into SQL Server Using SqlBulkCopy in C#
Apr 21, 2017 02:15 PM|kaushalparik27|LINK
It will be difficult to understand without what code you are writing for import. Would you post your code and specify line of code(s) where you see the issue?
[KaushaL] Blog Twitter [MS MVP 2008 & 2009] [MCC 2011] [MVP Reconnect 2017]
Don't forget to click "Mark as Answer" on the post that helped you
None
0 Points
8 Posts
Re: Import XML Bulk File into SQL Server Using SqlBulkCopy in C#
Apr 21, 2017 02:26 PM|hajiahmadkhan|LINK
I am using following class to import it into sql server by Using SqlBulkCopy
public abstract class BulkFileReader : IDataReader
{
#region DECLARATION
private readonly XmlReader oXmlReader;
public IEnumerator<XElement> oEnumerator;
private readonly string vRowElementName;
private readonly bool vIsMoveToContent;
private readonly int vFieldCount = -1;
private bool vDisposed;
#endregion
#region ABSTRACT METHODS
public abstract object GetValue(int pIndex);
#endregion
#region CONSTRUCTION
public BulkFileReader(XmlReader pXmlReader, int pFieldCount, string pRowElementName, bool pIsMoveToContent)
{
vRowElementName = pRowElementName;
vIsMoveToContent = pIsMoveToContent;
vFieldCount = pFieldCount;
oXmlReader = pXmlReader;
oEnumerator = GetXmlStream().GetEnumerator();
}
#endregion
#region PUBLIC METHODS
public bool Read()
{
return oEnumerator.MoveNext();
}
public int FieldCount
{
get { return vFieldCount; }
}
public XElement CurrentElement
{
get { return oEnumerator.Current; }
}
public void Dispose()
{
if (vDisposed)
return;
oEnumerator.Dispose();
vDisposed = true;
}
#endregion
#region PRIVATE METHODS
private IEnumerable<XElement> GetXmlStream()
{
XElement oRowElement;
using (oXmlReader)
{
if (vIsMoveToContent) oXmlReader.MoveToContent();
while (oXmlReader.Read())
{
Console.WriteLine(oXmlReader.Name);
if (IsRowElement())
{
oRowElement = XElement.ReadFrom(oXmlReader) as XElement;
if (oRowElement != null)
{
yield return oRowElement;
}
}
}
}
}
private bool IsRowElement()
{
if (oXmlReader.NodeType != XmlNodeType.Element)
return false;
return oXmlReader.Name == vRowElementName;
}
#endregion
}
}
None
0 Points
8 Posts
Re: Import XML Bulk File into SQL Server Using SqlBulkCopy in C#
Apr 21, 2017 02:29 PM|hajiahmadkhan|LINK
and initiate import by the following method in an other class
public void Import(string pConnectionString)
{
try
{
//using (oSqlBulkCopy = new SqlBulkCopy(pConnectionString, SqlBulkCopyOptions.Default))
using (oSqlBulkCopy = new SqlBulkCopy(pConnectionString,SqlBulkCopyOptions.CheckConstraints |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.KeepNulls |
SqlBulkCopyOptions.TableLock |
SqlBulkCopyOptions.UseInternalTransaction
))
{
string[] vColumns = { "vSupplierID", "vSupplierFlagType", "vSupplierFlagdescription", "vSupplierFlagValue", "vSupplierFlagLastVerificationDate", "vSupplierFlagDetail" };
string vTableName = "KNO_DSM_SupplierFlag_Temp";
Mapping.MapColumns(oSqlBulkCopy, vTableName, vColumns);
oSqlBulkCopy.BulkCopyTimeout = 60; // 1 minute
oSqlBulkCopy.BatchSize = 5000;
oSqlBulkCopy.EnableStreaming = true;
oSqlBulkCopy.WriteToServer(this);
}
}
catch
{
}
}
None
0 Points
8 Posts
Re: Import XML Bulk File into SQL Server Using SqlBulkCopy in C#
Apr 21, 2017 02:31 PM|hajiahmadkhan|LINK
public override object GetValue(int pIndex)
{
try
{
switch (pIndex)
{
case 0:
return vSupplierID;
case 1:
return CurrentElement.Element("SupplierFlagType").Value; ;
case 2:
return CurrentElement.Element("SupplierFlagDescription").Value;
case 3:
return CurrentElement.Element("SupplierFlagValue").Value;
case 4:
return string.IsNullOrEmpty(CurrentElement.Element("SupplierFlagLastVerificationDate").Value) ? "1900-01-01 00:00:00" : CurrentElement.Element("SupplierFlagLastVerificationDate").Value;
// return CurrentElement.Element("SupplierFlagLastVerificationDate").Value;
case 5:
return CurrentElement.Element("SupplierFlagDetails").Element("SupplierFlagDetail").Value;
default:
return null;
}
}
catch
{
return null;
}
}
All-Star
44931 Points
6838 Posts
Microsoft
Re: Import XML Bulk File into SQL Server Using SqlBulkCopy in C#
Apr 24, 2017 06:27 AM|Zhi Lv - MSFT|LINK
Hi hajiahmadkhan,
According to your code, I create a sample using the following code, it seems that everything works well, you could refer to it:
//create a memory stream with your xml content //var stream = new MemoryStream(Encoding.UTF8.GetBytes(@"...your xml content...")); var vColumns = new string[] { "vSupplierID", "vSupplierFlagType", "vSupplierFlagdescription", "vSupplierFlagValue", "vSupplierFlagLastVerificationDate", "vSupplierFlagDetail" }; var xmlReader = XmlReader.Create(stream); var reader = new BulkFileReader(xmlReader, vColumns.Length, "SupplierFlag", false); while (reader.Read()) { for (var i = 0; i < vColumns.Length; i++) { Console.WriteLine("{0,-35}: {1}", vColumns[i], reader.GetValue(i)); } Console.WriteLine(); }The output as below:
Best regards,
Dillion
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.