C# Code
- using System.Data.SqlClient;
- using System.Xml;
- SqlConnection con = new SqlConnection("Data Source=MTECH02\\MTECHDB;Initial Catalog=TESTQUERY;User ID=sa;pwd=sa@123");
- SqlCommand cmd;
- private void Form1_Load(object sender, EventArgs e)
- {
- cmd = new SqlCommand("SELECT * FROM TestData", con);
- con.Open();
- DataTable dt = new DataTable();
- SqlDataAdapter da = new SqlDataAdapter(cmd);
- cmd.CommandType = CommandType.Text;
- da.Fill(dt);
- dataGridView1.DataSource = dt;
- con.Close();
- }
- private void button1_Click(object sender, EventArgs e)
- {
- XmlDocument doc = new XmlDocument();
- XmlNode node = doc.CreateElement("r1");
- for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
- {
- XmlNode element = doc.CreateElement("t");
- XmlNode nd = doc.CreateElement("rot");
- nd = doc.CreateElement("i");
- nd.InnerText = dataGridView1.Rows[i].Cells[0].Value.ToString();
- element.AppendChild(nd);
- nd = doc.CreateElement("n");
- nd.InnerText = dataGridView1.Rows[i].Cells[1].Value.ToString();
- element.AppendChild(nd);
- nd = doc.CreateElement("a");
- nd.InnerText = dataGridView1.Rows[i].Cells[2].Value.ToString();
- element.AppendChild(nd);
- node.AppendChild(element);
- }
- cmd = new SqlCommand("XMLINSERTDATA", con);
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Parameters.Add(new SqlParameter("@XMLFormat", SqlDbType.Xml)).Value = node.OuterXml.ToString();
- con.Open();
- cmd.ExecuteNonQuery();
- con.Close();
- MessageBox.Show("SAVE DATA");
- }
SQL Table Create a 2 Tsble 1 DataGridview Bind And 2nd Bulk Insert.
Sql Procedure- USE[TESTQUERY]
- GO
- /****** Object: StoredProcedure [dbo].[XMLINSERTDATA] Script Date: 5/15/2016 7:54:07 AM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROC[dbo].[XMLINSERTDATA]
- (
- @XMLFormat XMl
- )
- AS
- BEGIN
- DECLARE @Integer int
- EXEC sp_xml_preparedocument @Integer OUTPUT, @XMLFormat
- SELECT ide, na, ad into# tempdata
- FROM OPENXML(@Integer, 'r1/t')
- WITH
- (
- ide int 'i',
- na varchar(50)
- 'n',
- ad varchar(50)
- 'a'
- )
- EXEC sp_xml_removedocument @Integer
-
- INSERT INTO XMLDATA
- (
- id,
- Nam,
- Adr
- )
- SELECT ide, na, ad
- FROM# tempdata
-
- END