I pass in three parameters to my stored procedure: @fileID, @public and @fileTypeID...How can I now update the two attributes Public and FileTypeID where FileID equals @fileID?
--*************************
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'
Please mark this post as Answer if it is of help to you!
I would love to change the world, but they wont give me the source code.
Tigers21
Member
487 Points
400 Posts
XML replace two attributes
Mar 30, 2012 06:28 PM|LINK
My XML look like this:
<Files>
<File FileID="1" Public="true" FileTypeID="5"></File>
<File FileID="2" Public="false" FileTypeID="5"></File>
</Files>
I pass in three parameters to my stored procedure: @fileID, @public and @fileTypeID...How can I now update the two attributes Public and FileTypeID where FileID equals @fileID?
santosh.jagd...
Star
7625 Points
1454 Posts
Re: XML replace two attributes
Mar 31, 2012 09:22 AM|LINK
you should use OPENXML function from SQL server. Create a stored proc and pass this xml file.
here is one format for OPENXML
MCP
kavita_khand...
Star
9767 Points
1930 Posts
Re: XML replace two attributes
Apr 02, 2012 09:16 AM|LINK
Run below script in your SQL query anaylzer
--lets say I have Stored procidure 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
('<Files>'+
'<File FileID="1" Public="true" FileTypeID="5"></File>'+
'<File FileID="2" Public="false" FileTypeID="5"></File>'+
'</Files>')
--*************************
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'
I would love to change the world, but they wont give me the source code.