tag:blogger.com,1999:blog-30031412.post3228118687871493335..comments2023-08-17T08:30:50.561+00:00Comments on Confessions of a Professional Developer: Manage multiple updates to Data using C#, TSQL and OPENXML in SQL ServerbUKaneerhttp://www.blogger.com/profile/06072329739628545853noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-30031412.post-1010050052585122952012-01-09T21:41:36.643+00:002012-01-09T21:41:36.643+00:00Bah! I'll send you a copy of the updated scri...Bah! I'll send you a copy of the updated script.Raymondnoreply@blogger.comtag:blogger.com,1999:blog-30031412.post-81188570413797756312012-01-09T21:30:09.729+00:002012-01-09T21:30:09.729+00:00Below are some very simple examples of inserting d...Below are some very simple examples of inserting data using XQuery. Using the WITH statement can be useful/tidier when you want to join the data from the XML to an existing table.<br /> <br />-- insert example<br />WITH data AS ( SELECT x.y.value('./id', 'int') AS identifier, x.y.value('./value', 'int') AS [value] FROM @xml.nodes('node') AS x(y))INSERT INTO ATable(ID, [Value], [AnotherValue])SELECT x.identifier, x.[Value], a.AnotherValueFROM data x JOIN AnotherTable a ON x.identifier = a.ID-- another insert exampleINSERT INTO ATable(ID, [Value])SELECT x.y.value('./id', 'int'), x.y.value('./value', 'int')FROM @xml.nodes('node') AS x(y)-- update exampleWITH data AS ( SELECT x.y.value('./id', 'int') AS identifier, x.y.value('./value', 'int') AS [Value] FROM @xml.nodes('node') AS x(y))UPDATE a SET a.[value] = x.[value]FROM Data x JOIN ATable a ON x.identifier = a.IDRaymondnoreply@blogger.comtag:blogger.com,1999:blog-30031412.post-68660893053605376632012-01-09T13:31:52.469+00:002012-01-09T13:31:52.469+00:00If you get chance I'd love to see that in acti...If you get chance I'd love to see that in action - perhaps update/add to the example and post me back I'll post it to the blog - giving you full credit of course!Luke Baughanhttp://ilovedevelopment.blogspot.com/noreply@blogger.comtag:blogger.com,1999:blog-30031412.post-52616447684215692612012-01-09T13:27:40.340+00:002012-01-09T13:27:40.340+00:00I've used a similar technique before, but inst...I've used a similar technique before, but instead of using a temporary/variable table I've updated data directly by using XQuery instead of OPENXML or queried the XML from within a WITH statement if I need to join the data with other database tables.Raymondnoreply@blogger.com