I have the following Stored Procedure query. This query is incomplete. I need help completing this query.
Problem:
In this SP, I am receiving a well-formed XML string into the SP. My goal is to update those rows in the Notes table that have the matching NoteId for the Notes passed in XML.
For example: If the XML has two notes with NoteId = 1, 2 and the table has three notes with NoteId = 1, 2, 3, I would like to update the NoteText of the table for rows with NoteId = 1, 2. Please help me complete my stored procedure.
Input XML:
<Notes>
<Note>
<NoteId>1</NoteId>
<NoteText>Hello</NoteText>
</Note>
<Note>
<NoteId>2</NoteId>
<NoteText>World</NoteText>
</Note>
</Notes>
Table before update:
NoteId | NoteText | IsDeleted
1 Hell 0
2 Worl 0
3 Test 0
Table after update needs to look like:
NoteId | NoteText | IsDeleted
1 Hello 0
2 World 0
3 Test 0
This is the Stored Procedure that I currently have:
ALTER PROCEDURE USP_Notes_UpsertNotes
(@notesToUpsert XML)
AS
BEGIN
SET NOCOUNT ON;
UPDATE Notes
SET NoteText = n.note_text
FROM ( select T.C.value('(NoteId/text())[1]','varchar(500)') as note_id,
T.C.value('(NoteText/text())[1]','varchar(500)') as note_text **PROBLEM**
from @notesToUpsert.nodes('/Notes/Note') as T(C)
) as n
WHERE n.note_id = Notes.NoteId AND Notes.IsDeleted = 0
END
GO
Error screenshot:
@notesToUpsert.nodes('/Notes/Note') as T(C)