How to save data using XML as input parameter SQL Server using C#.NET...!!

How to convert data into XML to transfer it as Parameter to save data into database using a datatable .

Step 1: create a function to parse the data using string parameter.

        private string ParseXpathString(string input)
        {
            if (input.Contains("'"))
            {
                int myindex = input.IndexOf("'");
                input = input.Insert(myindex, "'");
            }
            return input;
        }

Step 2:  Now convert the data into XML Data using the following code.

       string XMLData1;

       dt.TableName = "Table1";
          using (StringWriter sw = new StringWriter())
          {
              dt.WriteXml(sw, false);
              XMLData1 = ParseXpathString(sw.ToString());
          }

         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "";
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Connection = con;
         if (con.State == ConnectionState.Closed)
             con.Open();
         cmd.Parameters.AddWithValue("@XMLData1", XMLData1);
         cmd.ExecuteNonQuery();

Step 3: Create stored procedure to save the data in the appropriate table.

 

 CREATE PROCEDURE [dbo].[ProcedureName]

  @XMLData XML,  

  @return INT OUTPUT     

 AS
 BEGIN
 BEGIN TRANSACTION

            INSERT  INTO dbo.TableName
                    ( PId ,
                      CId ,
                      SId ,
                    )

            SELECT  CAST(colx.query('data(Col)') AS VARCHAR(20)) AS PId ,
                             CAST(colx.query('data(SizeId)') AS VARCHAR(50)) AS CId ,
                             CAST(colx.query('data(Price)') AS VARCHAR(50)) AS SId
                  INTO    #tmp
                  FROM    @XMLData.nodes('DocumentElement/Table1') AS Tabx ( Colx )

     
            IF @@ERROR <> 0
                BEGIN
                       ROLLBACK
                       SET @return=0
                END
            ELSE
                BEGIN
                       COMMIT
                       SET @return=1
                END
      END
GO