01/07 : Bulk Update With XML in SQL Server

C# Code to Convert Datatable To XML string

 

Func(…)

{

DataTable dt = new DataTable();

           DataColumn column;

           // Create new DataColumn, set DataType,
           // ColumnName and add to DataTable.   

           column = new DataColumn();
           column.DataType = System.Type.GetType("System.Int32");
           column.ColumnName = "TID";
           dt.Columns.Add(column);
           column = new DataColumn();
           column.DataType = System.Type.GetType("System.Int32");
           column.ColumnName = "ReturnErrorDesc";
           dt.Columns.Add(column);
           column = new DataColumn();
           column.DataType = System.Type.GetType("System.Int32");
           column.ColumnName = "Status";
           dt.Columns.Add(column);

           dt.Rows.Add(1, 4, 0);
           dt.Rows.Add(3, 3, 3);

string result=ConvertDataTableToXML(dt);

}

public static string ConvertDataTableToXML(DataTable dtBuildSQL)
        {
            DataSet dsBuildSQL = new DataSet();
            StringBuilder sbSQL;
            StringWriter swSQL;
            string XMLformat;

            sbSQL = new StringBuilder();
            swSQL = new StringWriter(sbSQL);
            dsBuildSQL.Merge(dtBuildSQL, true, MissingSchemaAction.AddWithKey);
            dsBuildSQL.Tables[0].TableName = "Row";
            foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)
            {
                col.ColumnMapping = MappingType.Attribute;
            }
            dsBuildSQL.WriteXml(swSQL, XmlWriteMode.IgnoreSchema);
            XMLformat = sbSQL.ToString();
            return XMLformat;
        }

 

 

SQL Query:

DECLARE @productIds xml

SET @productIds ='
<NewDataSet>
  <Row TID="1" ReturnErrorDesc="4" Status="0" />
  <Row TID="3" ReturnErrorDesc="3" Status="3" />
</NewDataSet>'

Declare  @temp  Table(
TID int,
ReturnErrorDesc int,
Status int
)

insert into @temp
SELECT
ParamValues.ID.value('@TID[1]','Int') as TID,
ParamValues.ID.value('@ReturnErrorDesc[1]','Int') as ReturnErrorDesc,
ParamValues.ID.value('@Status[1]','Int') as Status
FROM @productIds.nodes('/NewDataSet/Row')
as ParamValues(ID)

UPDATE    Smarteam_Transfer_01
SET              Smarteam_Transfer_01.Status =temp.Status,
                 Smarteam_Transfer_01.ReturnErrorDesc =temp.ReturnErrorDesc
From @temp temp
Where Smarteam_Transfer_01.TID=temp.TID

select * from Smarteam_Transfer_01