Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to insert xml file into sql server 2006 database structure using visual c# Expand / Collapse
Author
Message
Posted Monday, April 14, 2008 4:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2008 3:52 AM
Points: 1, Visits: 0
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
Post #484288
Posted Friday, February 1, 2013 1:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 1:23 PM
Points: 1, Visits: 1
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
Post #1414841
Posted Friday, February 1, 2013 1:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:05 PM
Points: 13,320, Visits: 12,804
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 Moden's 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)
Post #1414844
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse