Can it possible to perform multiple Update with XML format query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mohd Nizamuddin » Can it possible to perform multiple Update...

Can it possible to perform multiple Update with XML format query

Rate Topic

Display Mode

Topic Options

Author

Message

Nitesh katare

Posted Wednesday, October 13, 2010 3:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 15, 2010 7:59 AM
Points: 1, Visits: 4

I got an Idea how to Send multiple rows to the Database from an Application
and from the below code we can even insert it to the table as u showed in the Article

like,
consider a table Emp
INSERT INTO Emp (Name, Salary)

SELECT Name, Salary FROM OPENXML (@idoc, '/ROWS/ROW', 1)
WITH (Name varchar(50), Salary int)
WHERE Salary >= 1500
ORDER BY Name

My Doubt is about how to Update multiple rows in Emp table using the below xml query

<ROWS>
<ROW>
<ID>1</ID>
<Name>Tom</Name>
<Salary>21000</Salary>
</ROW>
<ROW>
<ID>2</ID>
<Name>Nitesh</Name>
<Salary>18000</Salary>
</ROW>
<ROW>
<ID>3</ID>
<Name>Mat</Name>
<Salary>15000</Salary>
</ROW>
</ROWS>

Please help me out for updating the table with above xml Query

Thank and Regards
Nitesh katare

Post #1003446

crackbridge

Posted Friday, October 15, 2010 12:49 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, July 04, 2011 6:16 AM
Points: 15, Visits: 48

Actually this is an interesting topic, but I would advice using SqlBulkCopy instead, which provide amazing performance, with just a few lines of code:

public virtual bool SqlBulkCopy(DataSet ds, string TableName) // provide ability to update any Table in the DS, assume TableName is idem for SQL and App Level
bool result = true;
using (SqlBulkCopy BulkCopy = new SqlBulkCopy(MyConnectionString))
{
BulkCopy.DestinationTableName = TableName;
try
{
BulkCopy.WriteToServer(ds.Tables[TableName]); // Write from the source to the destination.
}
catch (Exception e)
{
// Provide feedback of error issues, to be recovered using something like "DataSetErrorsShower.Show()"
ds.Tables[TableName].Rows[0].RowError = ExceptionHandler.WrapSqlException(e);
result = false;
}
if (result == true)
ds.Tables[TableName].AcceptChanges();
return result
}

Hope it helps, keep on comments.
Kind Regards,
Louis.

Post #1004927

« Prev Topic | Next Topic »

Permissions