how to insert xml file into sql server 2006 database structure using visual c#

  • i need help : i want to insert xml file into sql server2005 database table using c# such that i can access the file from c# console application

  • Hi, Here is an exampel.

    using System;

    using System.Collections.Generic;

    using System.Linq;

    using System.Text;

    using System.Data.SqlClient;

    using System.Xml;

    using System.Data;

    namespace XMLImportToSQL


    public class XMLImport


    //Create a Connectionstring to SQL Server

    // Trust connection (Server=;Database=;Trusted_Connection=True;)

    public static string SQLConn = "Server= Your server;Database= your database;User Id= User namn;Password= Password;"

    public void SaveXmlToSqlServer()


    //Create the XML Document for tranactions

    m_xmld = new XmlDocument();

    //Load the Xml file


    //Get the list of name nodes

    XmlNodeList financialyears = m_xmld.SelectNodes("/MainNod/Nod");

    //Loop through the XmlNode

    foreach (XmlNode financialyear in financialyears)


    // Get the Costcenter Attribute Value

    id = financialyear.ChildNodes.Item(0).InnerText;

    fromdate = financialyear.ChildNodes.Item(1).InnerText;

    todate = financialyear.ChildNodes.Item(2).InnerText;

    // Update if value has change else Insert new row

    using (SqlConnection conn = new SqlConnection(SQLConn))


    SqlCommand sqlCmdUpdate = new SqlCommand();

    sqlCmdUpdate = new SqlCommand("Update_Insert_Financialyears", conn);

    sqlCmdUpdate.CommandType = CommandType.StoredProcedure;

    sqlCmdUpdate.Parameters.AddWithValue("@id", id);

    sqlCmdUpdate.Parameters.AddWithValue("@fromdate", fromdate);

    sqlCmdUpdate.Parameters.AddWithValue("@todate", todate);

    // Check to see if the state is closed.

    if (conn.State == ConnectionState.Closed)


    // Open the connection.




    // Check to see if the state is open.

    if (conn.State == ConnectionState.Open)


    // Open the connection.








    Hope this will help you?


    Stefan Brand

  • Not sure they will see this. This was posted nearly 5 years ago and the poster has not logged in since then. 😀


    Need help? Help us help you.

    Read the article at for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns -
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs -
    Understanding and Using APPLY (Part 1) -
    Understanding and Using APPLY (Part 2) -

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply