Insert/Update in to SQL Database table using XML

Step (1) First Create XML file

XmlDocument XmlRoot = new XmlDocument();
XmlElement XmlChild = xml.CreateElement("Test");
XmlRoot.AppendChild(XmlChild);

CeateNewXmlChild(XmlRoot, XmlChild, “1”, “Yes”);

          

private void CeateNewXmlChild(XmlDocument XmlRoot, XmlElement XmlChild,string ID,string Flag)

{

XmlElement NewXmlChild = XmlRoot.CreateElement("Value");
NewXmlChild .SetAttribute("ID", ID);
NewXmlChild.SetAttribute("Flag", Flag);
XmlRoot.AppendChild(NewXmlChild);

}

Step(2) Created XML file will be as follows:

declare @data XML
SET @data = '<Test>
<Value ID="1" Flag="1"/>
<Value ID="2" Flag="0"/>
<Value ID="3" Flag="1"/>
</Test>'

Step(3) Now create XMLInsert Stored Procedure as below:

CREATE PROCEDURE [dbo].[usp_XMLTest]
(
        @Data XML
)
AS

BEGIN

UPDATE tbl1
             SET NeedApproval = C.value('@Flag','bit'),
             FROM @Data.nodes('/Test/Value') T(C)
             WHERE ID = C.value('@ID','int')

END

Step(4) Finally Calling SP as follows:

EXEC usp_XMLTest @data

I am referring below link for XML data types:

http://technet.microsoft.com/en-us/library/bb677236(v=sql.105).aspx