April 14, 2008 at 4:00 am
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
February 1, 2013 at 1:31 pm
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
February 1, 2013 at 1:47 pm
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/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply