Read XML Data in Sql Server

Solution :-


SELECT CAST(colx.query('data(RegisterationNumber)')  AS VARCHAR(50)) AS registerationnumber,
CAST(colx.query('data(SerialNo)') AS VARCHAR(50)) AS SerialNo,
CAST(colx.query('data(ScratchKey)') AS VARCHAR(50)) AS ScratchKey ,
                                    CAST(colx.query('data(ModOfPayment)') AS VARCHAR(50)) AS ModOfPayment ,
                                    CAST(colx.query('data(ContactNumber)') AS VARCHAR(50)) AS ContactNumber ,
                                    CAST(colx.query('data(IsAvailingLaundry)') AS VARCHAR(50)) AS IsAvailingLaundry ,
CAST(colx.query('data(IsAvailingFood)') AS VARCHAR(50)) AS IsAvailingFood ,
                                    CAST(colx.query('data(FoodType)') AS VARCHAR(50)) AS FoodType ,
CAST(colx.query('data(BookingDateTime)') AS VARCHAR(50)) AS BookingDateTime
INTO #tmp
FROM @XMLDATA.nodes('DocumentElement/mytable') AS Tabx (colx)


In aspx.cs Page (Example)


DataTable dtRecords = new DataTable();
            dtRecords.TableName = "mytable";
            dtRecords.Columns.Add(new DataColumn("RegisterationNumber"));
            dtRecords.Columns.Add(new DataColumn("SerialNo"));
            dtRecords.Columns.Add(new DataColumn("ScratchKey"));
            dtRecords.Columns.Add(new DataColumn("ModOfPayment"));
            dtRecords.Columns.Add(new DataColumn("ContactNumber"));
            dtRecords.Columns.Add(new DataColumn("IsAvailingLaundry"));
            dtRecords.Columns.Add(new DataColumn("IsAvailingFood"));
            dtRecords.Columns.Add(new DataColumn("FoodType"));
            dtRecords.Columns.Add(new DataColumn("BookingDateTime"));

            foreach (GridViewRow r in grdusers.Rows)
            {
                DataRow dr = dtRecords.NewRow();
                
                dr["RegisterationNumber"] = (r.FindControl("txtregistrationnumber") as TextBox).Text;
                dr["SerialNo"] = (r.FindControl("txtserialno") as TextBox).Text;
                dr["ScratchKey"] = (r.FindControl("txtscratchkey") as TextBox).Text;
                dr["ModOfPayment"] = ((r.FindControl("rblmodeofpayment") as RadioButtonList).SelectedIndex);  
                dr["ContactNumber"] = (r.FindControl("txtcontactno") as TextBox).Text;
                dr["IsAvailingLaundry"] = (r.FindControl("chklaundry") as CheckBox).Checked;
                if (((r.FindControl("rblfoodoption") as RadioButtonList).SelectedIndex == 0) || ((r.FindControl("rblfoodoption") as RadioButtonList).SelectedIndex == 1))
                {
                    dr["IsAvailingFood"] = "1";
                }
                else
                {
                    dr["IsAvailingFood"] = "0";
                }
                if (((r.FindControl("rblfoodoption") as RadioButtonList).SelectedIndex == 0) || ((r.FindControl("rblfoodoption") as RadioButtonList).SelectedIndex == 2))
                {
                    dr["FoodType"] = "1";
                }
                else
                {
                    dr["FoodType"] = "2";
                }
                
                dr["BookingDateTime"] = DateTime.Now;                 
                dtRecords.Rows.Add(dr);
            }
            string result;
            using (StringWriter sw = new StringWriter())
            {
                dtRecords.WriteXml(sw);
                result = sw.ToString();
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            if (con.State == ConnectionState.Closed)
                con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "[pHostelBookingInGroup]";
            cmd.Parameters.Add("@mytable", DbType.Xml).Value = result;
            cmd.ExecuteNonQuery();