none
How to develop a generic routine to insert data from xml to sql server RRS feed

  • Question

  • i have so many large xml file whose data i need to insert into database. so tell me what would be best way to do so using c#?

    should i use sql bulk copy class where i dynamically map xml field and db table field?

    tell me best approach which will be faster & efficient approach.

    Sunday, December 8, 2019 5:34 PM

Answers

  • Hi Sudip_inn,

    Thank you for posting here.

    For your question, you want to import the data in xml into the database.

    There are many ways to do this, but it is hard to say which one is the most efficient.

    This is a code sample, you can refer to it.

            static void Main(string[] args)
            {
                XmlReader xmlReader = XmlReader.Create(@"D:\test\xml\11.xml");
                xmlReader.MoveToContent();
                DataSet dataSet = new DataSet();
                dataSet.ReadXml(xmlReader);
                //Which table should be used depends on the format of the xml.
                DataTable dataTable = dataSet.Tables[2];
    
                InsertIntoSqlServer(dataTable, "Members");
            }
            public static void InsertIntoSqlServer(DataTable table, String tableName)
            {
                using (SqlConnection connection = new SqlConnection(@"ConnString"))
                {
                    connection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.DestinationTableName = $"dbo.{tableName}";
                        DataTableReader reader = table.CreateDataReader();
                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(reader);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.StackTrace);
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                }
            }
    

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Tuesday, December 10, 2019 7:59 AM
    Tuesday, December 10, 2019 5:46 AM

All replies

  • The first step is to have know each schema so that in code you have logic to determine the schema for a file then branch off to said schema and map the columns while a unknown schema would need to be evaluated the same as known schemas, have a logical branch of code to process.

    I used this idea for bulk copying Excel file contents into IBM-DB2 and SQL-Server database by identifying patterns in worksheets and similarly by reading patterns from PDF files so the same should apply to XML perhaps by looping through known schemas using schema validation, see the following for a start which validates one schema, you would need this in a loop or foreach.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, December 8, 2019 5:52 PM
    Moderator
  • Hi Sudip_inn,

    Thank you for posting here.

    For your question, you want to import the data in xml into the database.

    There are many ways to do this, but it is hard to say which one is the most efficient.

    This is a code sample, you can refer to it.

            static void Main(string[] args)
            {
                XmlReader xmlReader = XmlReader.Create(@"D:\test\xml\11.xml");
                xmlReader.MoveToContent();
                DataSet dataSet = new DataSet();
                dataSet.ReadXml(xmlReader);
                //Which table should be used depends on the format of the xml.
                DataTable dataTable = dataSet.Tables[2];
    
                InsertIntoSqlServer(dataTable, "Members");
            }
            public static void InsertIntoSqlServer(DataTable table, String tableName)
            {
                using (SqlConnection connection = new SqlConnection(@"ConnString"))
                {
                    connection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.DestinationTableName = $"dbo.{tableName}";
                        DataTableReader reader = table.CreateDataReader();
                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(reader);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.StackTrace);
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                }
            }
    

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Tuesday, December 10, 2019 7:59 AM
    Tuesday, December 10, 2019 5:46 AM
  • thanks for your reply. i have couple of question for your code given here.

    see this code

    XmlReader xmlReader = XmlReader.Create(@"D:\test\xml\11.xml");
                xmlReader.MoveToContent();
                DataSet dataSet = new DataSet();
                dataSet.ReadXml(xmlReader);
                //Which table should be used depends on the format of the xml.
                DataTable dataTable = dataSet.Tables[2];

    1) why did you use here XmlReader ? any special significant is there because your first read xml file by XmlReader and later load that XmlReader by dataset ? but we can directly read xml file by dataset. so why you use XmlReader and dataset together.......any special reason ?

    2) suppose my xml fileld name and db table column name is different then how could i map that dynamically in a routine? for this point please give a code which map xml field & table column. thanks

    Tuesday, December 10, 2019 7:59 AM
  • Hi Sudip_inn,

    It's my fault.

    XmlReader is not required.

    And for SqlBulkCopy, you do not need to consider the mapping problem, as long as the number and datatype of fields corresponds, it will be filled automatically, even if the names are inconsistent.

    This is all my code and it works fine.

    Best Regards, 

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 10, 2019 8:43 AM