informatic
New Member
I've been using the below sample code snippet to update a SQL Server 2005 database table. It converts a \[code\]List<T>\[/code\] where \[code\]T\[/code\] is a custom object with a series of properties (fields) into XML attributes on row elements nested inside a row root element.In this way, I make a huge series of inserts in one SQL transaction. Following this similar route, I wanted to provide an XML series of updates rather than inserts. I wasn't able to find any good sources on how I might adapt this approach for that. e.g. Say update B thru F based on a primary key A.\[code\]conn.Open();cmd = new SqlCommand();cmd.Connection = conn;cmd.CommandText = @"INSERT table (A,B,C,D,E,F) SELECT Tbl.Col.value('@A','nvarchar(50)'), Tbl.Col.value('@B','int'), Tbl.Col.value('@C','nvarchar(50)'), Tbl.Col.value('@D','nvarchar(1000)'), Tbl.Col.value('@E','nvarchar(50)'), Tbl.Col.value('@F','nvarchar(50)')FROM @xml.nodes('//row') Tbl(Col)";cmd.Parameters.Add("@xml", SqlDbType.Xml).Value = http://stackoverflow.com/questions/10658625/new XDocument(new XDeclaration("1.0", "utf-8", "yes"),new XElement("rows",toInsert.Select(p => new XElement("row", new XAttribute("A", x.Prop1), new XAttribute("B", x.Prop2), new XAttribute("C", x.Prop3), new XAttribute("D", x.Prop4), new XAttribute("E", x.Prop5), new XAttribute("F", x.Prop6))))).ToString();Console.WriteLine(cmd.ExecuteNonQuery().ToString() + " row(s) affected.");conn.Close();\[/code\]