I have the following Stored Procedure query. This query is incomplete. I need help completing this query.
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.
<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())','varchar(500)') as note_id, T.C.value('(NoteText/text())','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