Hi, I am new in asp.net. i have an task to read xml file from server folder and insert these values to sqlserver 2005 database.

how i can do this.


thanks in advance.......

Using server.mapmath read the excel file into dataset. dataset is having property called dataset.readxml. then you can insert the data into database.This is is one approach.. there may be any other...

XML and Excel file it is not the same.

If I need to read data from XML then I use something like that

System.Xml.XmlDocument myDoc = new System.Xml.XmlDocument();
myDoc.LoadXml(xmlString);
System.Xml.XmlElement root = myDoc.DocumentElement;
System.Xml.XmlNodeList myNodes = root.SelectNodes("node1/node2");

Then I iterate through myNodes and take necessary data with this code:

string myData = myNodes.SelectSingleNode("someNode").InnerText;

And then I insert data into a necessary query.

If you have a simple xml where one node has data for one records in a SQL table,

(like that:

<Records>
 <Record Field1="some text" Field2="9" />
 <Record Field1="some other text" Field2="10" />
</Records>
)

then you can pass XML directly to a stored procedure as a parameter (it must have type text)

DECLARE @Handle int
EXEC sp_xml_preparedocument @Handle OUTPUT, @MyXml 
	
INSERT INTO MyTable
(
	Field1,
        Field2,
)
SELECT 
	Field1,
        Field2,
	FROM 	OPENXML (@Handle, '/Records/Record', 1) 
WITH 
( 
	Field1 varchar(30),
        Field2 int
) 
	
EXEC sp_xml_removedocument @Handle

Hi Alex,

As bhagawatshinde mentioned he is having xml file which is to be red.
by mistake i mentioned as excel file. as i was working with excel at that moment.. :)
so i thought its easy to read into dataset and the iterate through dataset.
correct me if i am wrong.

Hi AlexERS and Pgmer thanks for reply. i will solved it with simpler manner here is my code

DataSet ds = new DataSet();
            ds.ReadXml(pathname);

            string testno = ""; string que_id = ""; string subcode = ""; string chapcode = ""; string que = ""; string opt1 = "";
         
            DataGrid dataGridView1 = new DataGrid();

            dataGridView1.DataSource = ds.Tables[0];
            for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {             
                
                que_id = ds.Tables[0].Rows[i][0].ToString();
                subcode = ds.Tables[0].Rows[i][1].ToString(); 
               
                cmd = new SqlCommand("TestInsert", Connect.Getconnection ());
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@id", SqlDbType.Int ).Value = que_id ;
                cmd.Parameters.Add("@name", SqlDbType.VarChar ).Value = subcode;
                
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            Response.Write("<script language='javascript' text='javascript'>alert ('Insert to database table Done it !!!!!!!!!!!!!')</script>");
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.