Member Avatar for bhagawatshinde

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

  • 4 Contributors
  • 6 Replies
  • 919 Views
  • 1 Year Discussion Span
  • Latest Post Latest Post by softwareskill

Dani AI

Generated

Brief, practical notes to complement the thread from , and .

The DataSet.ReadXml route used by is convenient for small, well-structured XML that maps cleanly to tables. The XmlDocument/XPath approach shown by (and the option to push XML into SQL Server) are valid when the XML is nested, has attributes, or must be validated first. Choice depends on XML shape and import volume: parse in .NET when business logic or cleaning is required; hand the XML to the database when the DB can efficiently shred it.

Important best practices and common pitfalls:

  • Do not open/close the DB connection or recreate the SqlCommand inside the row loop. Open once, reuse a prepared parameterized command, and set parameter values per row.
  • Match SqlParameter types to actual SQL types and convert strings to ints/dates before assigning; use DBNull.Value for missing data.
  • Wrap inserts in a transaction and use using blocks for deterministic disposal and error safety.
  • For large files prefer SqlBulkCopy into a staging table or pass the whole XML as an XML-typed parameter and use SQL Server XQuery (.nodes()/.value()) to shred on the server — both avoid per-row round trips.

Example pattern (reusable command, single connection/transaction):

using (var conn = new SqlConnection(connString))
{
    conn.Open();
    using (var tran = conn.BeginTransaction())
    using (var cmd = new SqlCommand("TestInsert", conn, tran))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
        cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 200));

        foreach (DataRow r in table.Rows)
        {
            cmd.Parameters["@id"].Value = int.TryParse(r["id"].ToString(), out var id) ? id : (object)DBNull.Value;
            cmd.Parameters["@name"].Value = string.IsNullOrEmpty(r["name"]?.ToString()) ? (object)DBNull.Value : r["name"];
            cmd.ExecuteNonQuery();
        }
        tran.Commit();
    }
}

Additional notes: watch for namespaces, element vs attribute mapping, encoding and XSD validation; add row-level logging for early troubleshooting.

Recommended Answers

All 6 Replies

Member Avatar for Pgmer

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

Member Avatar for AlexERS

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
Edited by kvprajapati because: Added [code] tags.
Member Avatar for Pgmer

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.

Edited by Pgmer because: n/a
Member Avatar for bhagawatshinde

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>");
Member Avatar for softwareskill

Nice article, here's another simple approach to read xml file in asp.net

Member Avatar for softwareskill

You are most welcome :)

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.