How to make a Stored Procedure that takes in XML and uses that xml as an Update + call this stored p
- by chobo2
Hi
I am using ms sql server 2005 and I want to do a mass update. I am thinking that I might be able to do it with sending an xml document to a stored procedure.
So I seen many examples on how to do it for insert
CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData XML)
AS
INSERT INTO
dbo.UserTable(CreateDate)
SELECT
@UpdatedProdData.value('(/ArrayOfUserTable/UserTable/CreateDate)[1]', 'DATETIME')
But I am not sure how it would look like for an update.
I am also unsure how do I pass in the xml through ado.net? Do I pass it as a string through a parameter or what?
I know sqlDataApater has a batch update method but I am using linq to sql. So I rather keep using it. So if this works I would be able to grab all records with linq to sql and have them as objects. Then manipulate the objects and use xml seralization.
Finally I could just use ado.net simple to send the xml to the server. This might be slower then the sqlDataAdapter but I am willing to take that hit if I can keep using objects.