How to use XML DataType of SQL Server with C#.NET

Some time we required to insert XML data in sql server through the use of C#.NET, You can do it after execution the some line of code.

1) create an XML formated data in C# either using loop or from any other way, for eg.


2) create an stored procedure in sql server

CREATE PROCEDURE [dbo].[ProcedureName]

(

@XMLData XML

)

AS

BEGIN

UPDATE table SET PRICE_1YEAR=TempData.Item.value('@PRICE_1YEAR'

FROM @XMLData.nodes('/root/row') AS TempData(Item)

      WHERE ID=TempData.Item.value('@ID', 'INT') and PRODUCT_ID =TempData.Item.value('@PRODUCT_ID', 'INT')END