Mass update sql server table using xml

Question

How can I update bulk data in SQL Server 2005 by sending data in XML form? I can insert bulk data into a table, but I am not going to update the data.

Answer 1

Insert into #TempTable //Basically do bulk insert into temp table then... Update MyTable Set Field1 = tt.Field1, Field2 = tt.Field2, ... FROM #TempTable tt where primaryKey = tt.PrimaryKey 

Please note that this is good suedo code. therefore replace fieldx with your field names and replace primaryKey with the primarykey field name or the unique identifier field name for the table.

Answer 2

SQL Server 2005 and later has built-in support for XML data types, and also supports the XQuery language for shredding XML into columns of relational data.

Check out Introducing XQuery in SQL Server 2005 to understand how it works.

Answer 3

To update

some external logic needs to be defined.

for example, if the primary key of an incoming record already exists, update the other columns, otherwise insert this record.

I can suggest you write some xslt to create update statements from the incoming XML stream, and then run this sql script.

Answer 4

Check out OPENXML . You also want to load the XML data into a variable table so that you can easily combine or compose the rest of the query, just like between regular database tables.

Answer 5

To update the values ​​in the table, use

 CREATE PROCEDURE UpdEmpd @UpdEmpd xml AS BEGIN UPDATE HumanResources.EmployeeData SET Salary=3000.00 WHERE EmployeeID='E15' END 

Run:

 Exec UpdEmpd '<Record xmlns:xsi="http://www.w3.org/2012/xmlschema-instance"> <HumanResources.EmployeeData> <Salary>3000.00</Salary> <EmployeeID>E15</EmployeeID> </HumanResources.EmployeeData> </Record>'