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

• Raymond-710502

  Valued Member

  Points: 59

  More actions

  April 14, 2008 at 4:00 am

  #95911

  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

• brandstefan

  SSC Rookie

  Points: 27

  More actions

  February 1, 2013 at 1:31 pm

  #1583587

  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

  m_xmld.Load(@"C:\XmlFile.xml");

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

  conn.Open();

  }

  sqlCmdUpdate.ExecuteNonQuery();

  // Check to see if the state is open.

  if (conn.State == ConnectionState.Open)

  {

  // Open the connection.

  conn.Close();

  }

  }

  }

  }

  }

  }

  Hope this will help you?

  Regads

  Stefan Brand

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  February 1, 2013 at 1:47 pm

  #1583590

  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 http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/