locked
Import XML Bulk File into SQL Server Using SqlBulkCopy in C# RRS feed

  • Question

  • User-1873611554 posted

    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

    Friday, April 21, 2017 1:28 PM

All replies

  • User-1509636757 posted

    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?

    Friday, April 21, 2017 2:15 PM
  • User-1873611554 posted

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

    Friday, April 21, 2017 2:26 PM
  • User-1873611554 posted

    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
    {

    }
    }

    Friday, April 21, 2017 2:29 PM
  • User-1873611554 posted

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

    Friday, April 21, 2017 2:31 PM
  • User-271186128 posted

    Hi hajiahmadkhan,

    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

    Monday, April 24, 2017 6:27 AM