You would like to update a particular attribute
using a stored procedure. To so the same follow below steps.
*******************************************************
Run below script in your SQL query analyzer
*******************************************************
--lets say I have Stored procedure as below.
Alter PROCEDURE updateFileTable
(
@file NVARCHAR(100),
@public NVARCHAR(100),
@fileTypeId NVARCHAR(100)
)
AS
-- Step 1 - Creating a Temporary table.
CREATE TABLE #FileTable
(
cFileName XML,
)
-- Step 2 - Inserting the test data give by you.
INSERT INTO #FileTable
([cFileName])
VALUES
('
'
'
'
----------------------------
PRINT 'Before Update'
SELECT * FROM #FileTable
----------------------------
--Step 3 - Updating the data.
-- here, in just tje below line the last 1 signifies that the while condition has returned true, and only then the update will take place
WHILE EXISTS (SELECT * FROM #FileTable WHERE cFileName.exist('/Files//File/@FileID[.=sql:variable("@file")]') = '1')
UPDATE #FileTable
SET cFileName.modify('replace value of (/Files//File/@FileID[.="1"])[1] with "10"')
----------------------------
PRINT 'After Update'
SELECT * FROM #FileTable
----------------------------
DROP table #FileTable
*******************************************************
Now EXECUTE the Stored PROCEDURE USING below STATEMENT.
*******************************************************
EXEC updateFileTable '1','false','9'
