Store XML File in SQL Data Base

[farag]

Hello,

i want to know the best practice to
1. read an XML file and store it in SQL data base and
2. modify the data base whenever the XML file modified,
i created a table on data base on SQL server with columns has the same names like XML file attributes

Sample of XML File

<?xml version="1.0" encoding="utf-8" ?> 
- <SYSTEM>
  - 
- <message>
  <CarsId>11</CarsId> 
  <CarsModel>Toyota</CarsModel> 
  <CarsColor>Green</CarsColor> 
  </message>
  - 
- <message>
  <CarsId>12</CarsId> 
  <CarsModel>Kia</CarsModel> 
  <CarsColor>Yellow</CarsColor> 
  </message>
  </SYSTEM>

i searched the web, But i didn't find what i need, So anyone Can Help :)

[kvprajapati]

>Read an XML file and store it in SQL data base and modify the data base whenever the XML file modified.

Read this article - SQLXML.

[alc6379]

What I've been doing is using a stored procedure to read in the XML file as a string, then using OPENXML to get a table from the XML file. I'm just inserting a number of rows in the table, though-- I'm not updating.

This link helped me a bunch:
http://msdn.microsoft.com/en-us/library/aa276847(SQL.80).aspx

[farag]

thanks all, with discussion with my peers i got my solution and here u r to see it:

1. read an XML file and store it in SQL data base and
Sol. Using of XmlDocument, XmlNodeList and XmlNode classes to manipulate the XML file

2. modify the data base whenever the XML file modified,
Sol. Using of FileSystemWatcher Class to watch file modification.

i created a table on data base on SQL server with columns has the same names like XML file attributes
Sol. Using of SqlConnection and SqlCommand to manipulate the SQL Database.

if u need the whole Sol., Don't hesitate to ask me :)....

[h612]

thanks all, with discussion with my peers i got my solution and here u r to see it:

1. read an XML file and store it in SQL data base and
Sol. Using of XmlDocument, XmlNodeList and XmlNode classes to manipulate the XML file

2. modify the data base whenever the XML file modified,
Sol. Using of FileSystemWatcher Class to watch file modification.

i created a table on data base on SQL server with columns has the same names like XML file attributes
Sol. Using of SqlConnection and SqlCommand to manipulate the SQL Database.

if u need the whole Sol., Don't hesitate to ask me :)....

hello- i need to see the solution could you please send it to me? i'm workign on a project and its been weeks i've been unable to figure it out.. Kinldy help-

[farag]

Dear h612, kindly find the Sol. here, This Sol. is for my program u can customize this Sol.to fit urs,

1. i created a table on data base on SQL server with columns has the same names like XML file attributes
Sol. Using of SqlConnection and SqlCommand to manipulate the SQL Database.

string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True";

SqlConnection sqlConn = new SqlConnection(connectionString);
sqlConn.Open();

SqlCommand sqlCmnd = new SqlCommand();
sqlCmnd.Connection = sqlConn;

2. read an XML file and store it in SQL data base and
Sol. Using of XmlDocument, XmlNodeList and XmlNode classes to manipulate the XML file

string sXMLFilePath = @"C:\car_task.xml";

XmlDocument document = new XmlDocument();
document.Load(sXMLFilePath);

Car MyCar = new Car();
XmlNodeList xmlNodeList = document.SelectNodes("SYSTEM/message");
foreach (XmlNode node in xmlNodeList)
{
	MyCar.CarID = int.Parse(node["CarsId"].InnerText);
	MyCar.CarModel = node["CarsModel"].InnerText;
	MyCar.CarColor = node["CarsColor"].InnerText;

	sqlCmnd.CommandText = "INSERT INTO CARS (CarsID,CarsModel,CarsColor) " +
		"Values(" + MyCar.CarID + ",'" + MyCar.CarModel + "','" + MyCar.CarColor + "')";
	sqlCmnd.ExecuteNonQuery();
}

3. modify the data base whenever the XML file modified,
Sol. Using of FileSystemWatcher Class to watch file modification.

u can use this link to read about the FileSystemWatcher Class

Hoping this is Helpful :) ....